跳至主要内容

[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。