PostgreSQL 的 Deferrable Constraints

Constraints 一般在 PostgreSQL 裡面都是一行一行的驗證,而且是立刻驗證。

舉個範例,下面是一個 numbers table 裡面只有一個 column ,就是 number,同時加上一個 Unique Constraint.

CREATE TABLE numbers (
    number INT,
    UNIQUE (number)
);

INSERT INTO numbers
VALUES (1)
     , (2);

如果希望 table 裡的資料,都 +1 ,透過以下的 script 執行就會得到 duplicate 的錯誤。

UPDATE numbers
SET number = number + 1;

-- ERROR:  duplicate key value violates unique constraint "numbers_number_key"
-- DETAIL:  Key (number)=(2) already exists.

比較 Immediate vs. deferred

在 PostgreSQL 建立 constrained ,後面隱含的是 NOT DEFERRABLE INITIALLY IMMEDIATE

把四個字切一半:

NOT DEFERRABLE

  • 沒得商量,即便在 Transaction 裡面,也需要符合立刻檢查的規則。

INITIALLY IMMEDIATE

  • 代表資料更新的時候都會立刻做資料上的驗證。

所以經過排列組合,可以得到四種選項,扣掉一種剩三種。

  • NOT DEFERRABLE INITIALLY IMMEDIATE
    • Postgrel 建立 constraint 的預設選項,即便在 transaction 裡,也需要一行一行的立刻檢查。
  • DEFERRABLE INITIALLY IMMEDIATE
    • 這是指資料的檢查,可以在語句執行完後,再統一檢查。同時也可以在每個 transaction 裡面做設 IMMEDIATE or DEFFERRED 的設定。
  • DEFERRABLE INITIALLY DEFERRED
    • 這是指資料的檢查,可以在 transaction 以後再檢查。
  • NOT DEFERRABLE INITIALLY DEFERRED
    • 這兩個組合,是矛盾的,所以不會有這種設定方法,又不可以在 transaction 結

可以 deferred 的 constrains

  • Primary Key (建議不要這麼做,可能會有一些 performance 的考量。
  • UNIQUE
  • REFERENCES (foreign key)
  • EXCLUDE

不可以 deferred 的 constrains

  • NOT NULL
  • CHECK

References:

Scroll to Top