[PSQL] PostgreSQL Constraints
Section 13: Understanding PostgreSQL Constraints @ postgresqltutorial
Foreign Key
PostgreSQL Foreign Key @ postgresqltutorial: Section 13
Foreign key(FK)是在資料表中用來指稱到其他表 PK(Primary Key)的欄位,其中包含有 foreign key 的資料表稱作 referencing table 或 child table;會被 foreign key 指稱到的表則稱作 referenced table 或 parent table。
在 Constraint 可以特別透過 ON UPDATE
和 ON DELETE
來指定「當 parent 資料表的 PK 被更新或刪除時」要做的事情,但一般來說 PK 並不會改變,因此 ON UPDATE
是較少用到的,比較常用到的是 ON DELETE
。
語法
-- 使用 CONSTRAINT 定義 fk 的名稱
-- 使用 FOREIGN KEY 定義哪個欄位是 FK
-- 使用 REFERENCES parent_table() 定義另一個資料表的哪個欄位是要被參照的 PK
[CONSTRAINT fk_name]
FOREIGN KEY(fk_columns)
REFERENCES parent_table(parent_key_columns)
[ON DELETE delete_action]
[ON UPDATE update_action]
範例
建立新的表
- Customer has many Contacts
- Contact belongs to Customer
CREATE TABLE contacts(
contact_id INT GENERATED ALWAYS AS IDENTITY,
customer_id INT,
contact_name VARCHAR(255) NOT NULL,
PRIMARY KEY(contact_id),
CONSTRAINT fk_customer
FOREIGN KEY(customer_id)
REFERENCES customers(customer_id)
ON DELETE CASCADE
);
將原有的欄位加上 constraint
-- 幫 users 資料表先增 organization_id 的欄位
ALTER TABLE users ADD COLUMN organization_id INT;
-- 幫 users 資料表中的 organization_id 欄位新增 Constraint
ALTER TABLE users
ADD CONSTRAINT fk_users_organization
FOREIGN KEY(organization_id)
REFERENCES organizations(id);
將原有的欄位移除 constraint
ALTER TABLE child_table
DROP CONSTRAINT constraint_fkey;
設定 ACTION
SET NULL
:一旦 parent table 被移除後,child table 的 FK 設為null
SET DEFAULT
:RESTRICT
NO ACTION
(預設):和RESTRICT
類似,當什麼都不做時,無法把已經有被 child table 關聯到 record 刪除。CASCADE
NO ACTION(預設值)
Insert or Update
建立或更新資料的時候,FK 的值可以是 nil
,但如果不是 nil
的話,需要確定 referenced table 的 PK 真的存在,否則會報錯。例如,Customer 有很多 Contacts:
-- customer_id 是 Null 則可以建立
INSERT INTO contacts(contact_name, customer_id)
VALUES('Aaron', NULL);
如果 customer_id
不是 nil
,即使是 0
也會報錯:
INSERT INTO contacts(contact_name, customer_id)
VALUES('Aaron', 0);
錯誤訊息:
ERROR: insert or update on table "contacts" violates foreign key constraint "fk_customer"
DETAIL: Key (customer_id)=(0) is not present in table "customers".
Delete
當 parent table 中的資料被刪除時,ON DELETE NO ACTION
對 child table 什麼都不會做,導致 child table 中的 FK 找不到 parent table 中對應的 PK 而噴錯。
SET NULL
當 parent table 中的資料被刪除時,ON DELETE SET NULL
會把在 child table 中,與該 parent table 資料有關聯的資料的 FK 都設成 null
。
情境一樣是 Customer 有很多 Contacts:
CREATE TABLE contacts(
contact_id INT GENERATED ALWAYS AS IDENTITY,
customer_id INT,
PRIMARY KEY(contact_id),
CONSTRAINT fk_customer
FOREIGN KEY(customer_id)
REFERENCES customers(customer_id)
ON DELETE SET NULL
);
這時候 若我們一樣把某一個 Customer 刪除時,因為我們在 ON DELETE
時有設定 SET NULL
的 Action,因此不會噴錯,而是不把與該 Customer 對應的 Contacts 中的 FK(即,customer_id
設成 null
)。
CASCADE(最常見)
當 parent table 中的資料被刪除時,ON DELETE CASCADE
會自動把在 child table 中,與 parent table 相關聯到 record 都一併刪除。實務上這也是最常見的做法。
SET DEFAULT
當 parent table 中的資料被刪除時,ON DELETE SET DEFAULT
會自動把在 child table 中,與 parent table 相關聯到的 FK 設為 default value。