昨天介紹過如何使用 Check Constraints , CHECK
使用上雖然很方便,但要注意的是,CHECK
最好只針對單一筆資料做條件的判斷,不要跨多筆資料,不然當其他筆資料變動時, CHECK
所檢查的條件會跟之前的不一樣,而且使用者並不知道。
舉例來說, employees
記錄公司有哪些員工和薪資,今天公司想要提升薪資水平,所以新人和員工變動之後的薪資,不得低於現有平均,於是使用 CHECK (salary >= avg_salary())
檢查。
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
salary NUMERIC
);
CREATE FUNCTION avg_salary() RETURNS NUMERIC AS $$
SELECT AVG(salary) FROM employees;
$$ LANGUAGE SQL;
ALTER TABLE employees ADD CHECK (salary >= avg_salary())
今天有兩名新進員工的薪水平均是5500。
INSERT INTO employees (name, salary) VALUES ('Alice', 5000);
INSERT INTO employees (name, salary) VALUES ('Bob', 6000);
後來在招聘了 Charlie ,給的薪水是5499,因為 5499 < 5500,輸入的時候會被拒絕。
但後來在人資調整了 Bob 的薪資之後,給 Charlie 5499就又可以了,這意味著 CHECK (salary >= avg_salary())
這個條件無時無刻都在變動,想像一下,如果需要用pg_dump/pg_restore備份和還原資料庫,因為 avg_salary()
隨時在變動,而且條件是綁在table上,很有可以能會在還原的過程中Insert失敗。
如果真的得這麼做,建議使用trigger, trigger function 裡可以加判斷,例如偵測某個 session 或 flag,來決定是否暫時跳過檢查,或者在還原期間關掉所有trigger,避免還原時失敗。
set -euo pipefail
DB="${1:?target DB name required}"
DUMP_FILE="${2:?path to .dump/.tar required}"
pg_restore \
--dbname="$DB" \
--single-transaction \
--disable-triggers \
--no-owner --no-privileges \
"$DUMP_FILE"