Skip to main content

[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 tablechild table;會被 foreign key 指稱到的表則稱作 referenced tableparent table

在 Constraint 可以特別透過 ON UPDATEON 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。

Last updated on