# 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