SQL server foreign key constraint

額外包含 SQLAlchemy 的設定

SQL Server

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

  • CASCADE:不同於 SET NULLSET 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。目前只有 UNIQUEPRIMARY KEYEXCLUDEREFERENCES(foreign key) 接受此設定。NOT NULLCHECK 則是 NOT DEFERRABLE。 請注意,在包含 ON CONFLICT DO UPDATEINSERT 指令中,延遲 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-updatemergerefresh-expireexpungedelete 這幾種的縮寫

Reference

PostgreSQL

MySQL

SQLAlchemy