前言

這篇文章主要是探討 PostgreSQL 的 Constraints 情境下的幾種 ACTION，以作為一個簡單的學習紀錄

何謂 Constraint？

眾所皆知，Foreign key(FK) 是 Table 中一個或一組用來關聯另一張表的 Primary key(PK) 的欄位，其中：

  • 關聯另張表的表叫做 referencing table 或 child table
  • 被關聯的表則稱為 referenced table 或 parent table

當 parent table 的某欄位和 child table 的某欄位有關聯時，例如: PK(Primary Key)和 FK(Foreign Key)的關係時，此時對 parent table 這個欄位做出修改(update)或刪除(delete)的操作，勢必會連帶影響到 child table。此時，child table 就需要明確的告訴 parent table，該筆操作對 child table的欄位要有什麼影響(ACTION)?

所以如何決定這些跨表參照欄位的存亡關係，就取決於 Constraint 的設定．換言之，SQL 透過 Constraint 的限制來協助我們維持父子表之間的參照完整性(referential integrity)

而 Constraint 又分為被更新(ON_UPDATE)以及被刪除(ON_DELETE)時，分別有對應的影響(ACTION)可以設定，實務上有 PK 和 FK 關係的欄位，大多會用 ON_DELETE居多．

在 PostgreSQL 中的語法結構大致如下(參考該網站)：

[CONSTRAINT fk_name]
   FOREIGN KEY(fk_columns)
   REFERENCES parent_table(parent_key_columns)
   [ON DELETE delete_action]
   [ON UPDATE update_action]

ACTION 有哪些？

可設置的 delete_action 和 update_action 都稱為 ACTION，分為以下幾種:

  • NO ACTION: 是預設值，一旦 parent table 被刪除或修改時，若 child table 尚有關聯到 parent table 的 FK 欄位資料，將會 raise error
  • SET NULL: 一旦 parent table 被刪除或修改時，child table 所關聯的 FK 欄位值將會設定成 null
  • SET DEFAULT: 一旦 parent table 被刪除或修改時，child table 所關聯的 FK 欄位值將會設定成 DEFAULT 的值
  • RESTRICT: 與 NO ACTION類似，都會 raise error，唯一差別是該 FK 有無 deferable，我個人使用上較少用
  • CASCADE: 最常用，一旦 parent table 被刪除或修改時，child table 所關聯的 FK 欄位值也將會一併刪除或修改

文章與留言的 CASCADE 例子

關於 CASCADE 的情境如下

一篇文章有多個留言(1:N):

  • parent table: article 文章
  • child table: comment 留言

建立 parent table(article)的 SQL 語法如下:

CREATE TABLE IF NOT EXISTS article(
    id serial NOT NULL, --id(獨特性)，ex: 1
    article_id text,    --文章的id，ex: panda_0402
    title text,         --文章標題，ex: 熊貓的一天
    author text,        --作者，ex: 小熊貓
    content text,       --內文，ex: 今天星期六，101是靛色
    CONSTRAINT pk_article_id PRIMARY KEY (article_id)
	  --最後一句: 設定一個名稱叫做pk_article_id的constraint，並設定article_id為PK
);

建立 child table(comment)的 SQL 語法如下:

CREATE TABLE IF NOT EXISTS "comment"(
    id serial NOT NULL,   --id(獨特性)，ex: 1
    article_id text,      --文章的id，ex: panda_0402
    push_user_id text,    --推文作者，ex: madi
    push_content text,    --推文內容，ex: 靛色是什麼顏色?
    CONSTRAINT fk_article_id FOREIGN KEY(article_id) REFERENCES article(article_id)
		--最後一句: 設定一個名稱叫做fk_article_id的constraint，並設定comment這個table的article_id欄位為FK，關連到article這個table的article_id欄位
);

ERD

接下來手動增加資料

--對article新增一筆資料
insert into article(article_id, title, author, "content") values ('panda_0402', '熊貓的一天', '小熊貓', '今天星期六，101是靛色');

--對comment新增一筆資料
insert into "comment"(article_id, push_user_id, push_content) values ('panda_0402', 'madi', '靛色是什麼顏色?');

查看看有沒有新增成功

select * from article

article

select * from "comment"

comment

OK，確認資料都新增成功

再來是嘗試刪除掉 parent table(article)中的這筆資料，看看會發生什麼事情

delete from article where article_id='panda_0402';

噴了一個錯誤!!

error

錯誤是說，article 這個 table 的 article_id 有被其他人給關聯到，所以違反了 comment 這個 table 中名為 fk_article_id 的 constraint，因此不能刪除掉

也就是上面建立 child table 的 schema 時的這一行 CONSTRAINT fk_article_id FOREIGN KEY(article_id) REFERENCES article(article_id) 造成的

求證一下資料是否還在

select * from article where article_id='panda_0402'

article2

資料確實還在，所以證明的確沒有刪除成功

這時候腦袋有個想法，既然 parent table 因為被 child table 給 referenced 到，所以不能刪，那 child table 沒有被其他 table 給 referenced 到，總可以刪掉了吧?

所以嘗試刪看看 comment table 的一筆資料，看會不會遇到同樣的問題?

delete from "comment" where article_id='panda_0402';

success

結果真的被刪掉了欸!

來想想前後兩個刪除方法差在哪裡

第一個刪除，article table 的 article_id 不能被刪掉是因為他被 comment 的 foreign key(article_id)給 referenced 住。

第二個刪除，comment 的 article_id 並沒有被其他 table 用 foreign key 給 referenced 住，所以是可以刪掉的

因此，我們得到第一個結論:

  • 當 parent table 被 child table 用 foreign key 給 referenced 住，就無法刪掉!
  • 但如果某個 table 沒有被其他 table 給 referenced 住，就可以刪掉!

理解了之後，那我們到底該怎麼刪掉 parent table(article)中的資料呢?

答案就是使用 CASCADE！

創建 comment 這個 child table 的 SQL 語法應該改為底下這樣

CREATE TABLE IF NOT EXISTS "comment"(
    id serial NOT NULL,   --id(獨特性)，ex: 1
    article_id text,      --文章的id，ex: panda_0402
    push_user_id text,    --推文作者，ex: madi
    push_content text,    --推文內容，ex: 靛色是什麼顏色?
    CONSTRAINT fk_article_id FOREIGN KEY(article_id) REFERENCES article(article_id) ON DELETE CASCADE
)

主要是最後一句加上 ON DELETE CASCADE ，意思是指，child table(comment)明確的告訴 parent table(article)說，當你某個資料要被刪除的時候，不用再管我了，直接把我也刪掉吧，把刪除地指令一併的執行給 child table，所以CASCADE 是關聯性的意思

建立好上面新的 schema 並重新新增資料後，我們再刪除 parent table(article)看看

delete from article where article_id='panda_0402';

success2

成功刪除了!!!

所以我們得到第二個結論:

  • CASCADE 是指把對 parent table 的操作一併的執行給 child table，等同於連鎖反應，parent 與 child 共生死，確保資料的一致性。
  • 須特別注意，這個 ACTION 是定義在 child table 上，而不是 parent table。 意思是說 ON DELETE CASCADE 是寫在 child table(comment)的 schema 裡，而不是 article 這個 table 裡面 (想一想後覺得很合理! 因為父母(parent)今天要孩子(child)改名(update)，應該是孩子(child)自己決定(ACTION)才對，怎麼能讓父母(parent)武斷決定呢?)

總結

簡單來說，Constraint 束縛跨表參照的欄位之間的限制，而當 parent table 的欄位修改或刪除時，如何決定 child table 中與其關聯的這些欄位去留，就得仰賴 ACTION 的設置，而本篇文章提到的 ACTION 就是在規定任何對 parent table 的操作(update or delete)，該怎麼影響給有 referenced 到 parent table 的那些 child table 呢?

  • 是該一併執行呢? (CASCADE)
  • 還是捨棄執行且噴錯呢? (RESTRICT, NO ACTION)
  • 又或者是設置 null 或其他預設值呢？ (SET NULL, SET DEFAULT)