SQL server foreign key constraint

SQL Server

常見的 SQL server 的 foreign key constraint，通常使用在 ON UPDATE 和 ON DELETE 的設定，有以下幾種 Referential Actions：

• CASCADE：不同於 SET NULL 和 SET DEFAULT，會修改 foreign key 欄位得值。此設定會在 parent 資料被刪除的時候，將關聯的資料也一並刪除。
• NO ACTION：預設設定。產生一個錯誤，宣告 delete 或 update 會造成 foreign key constraint 違規。如果 constraint 被延遲，則會在約束檢查時產生此錯誤。
• RESTRICT：相似於 NO ACTION，不同的在於此設定只做檢查，但不會延期(deferrable)
• SET DEFAULT：此設定會在當 parent 的資料被刪除的時候，將 child table 的關聯資料的 foreign key 自動設定為預設值。
• SET NULL：此設定會在當 parent 的資料被刪除的時候，將 child table 的關聯資料的 foreign key 自動設為 NULL。

這邊要特別提到以下的設定

deferrable & not deferrable

此設定是用來控制 constraint 是否可以 deferrable。not deferrable 的 constraint 都會在每個指令之後立即檢查。但如果是 deferrable 的 constraint 則會等到 transaction 結束的時候才做檢查（使用 SET CONSTRAINTS）。 其中預設值都是 NOT DEFERRABLE。目前只有 UNIQUE、PRIMARY KEY、EXCLUDE 和 REFERENCES(foreign key) 接受此設定。NOT NULL 和 CHECK 則是 NOT DEFERRABLE。 請注意，在包含 ON CONFLICT DO UPDATE 的 INSERT 指令中，延遲 constraint 不能用於衝突仲裁器

initially immediate & initially deferred

如果 constraint 是可延遲的，則該指令指定檢查 constraint 的預設時間。如果 constraint 是 INITIALLY IMMEDIATE，則在每個語句之後檢查它，這是預設設定。如果 constraint 是 INITIALLY DEFERRED，則會在 transaction 結束時對其進行檢查。可以使用 SET CONSTRAINTS 命令更改 constraint 檢查時間。

SQLAlchemy

• save-update：預設設定。新增一條資料的時候，會把其他相關的資料也新增到資料庫
• delete：刪除資料時，其相關的資料也會刪除
• delete-orphan：適用於一對多，不能用於多對多或多對一。並且在使用的時候需要在 child model 的 relationship 中增加參數 single_parent=True
• merge：預設設定。使用時合併一個物件的時候，會將使用了 relationship 相關的物件也會進行 merge
• expunge：移除操作的時候，會將關聯的物件也進行刪除，但此操作只存在於 session，並不會從資料庫刪除
• all：是 save-update、merge、refresh-expire、expunge、delete 這幾種的縮寫

Reference

PostgreSQL

• https://www.postgresql.org/docs/13/sql-createtable.html
• https://www.postgresqltutorial.com/postgresql-foreign-key/

MySQL

• https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html

SQLAlchemy

• https://www.cnblogs.com/kirito-c/p/10900024.html
• https://pjchender.dev/database/psql-constraints/
• https://docs.sqlalchemy.org/en/14/orm/cascades.html
• https://www.cnblogs.com/zhongyehai/p/11816921.html