基於另一列中的值的列約束

我有一個 PostgreSQL 表phase_steps，具有以下範例行：

phase_step_id|step_type|step_status|
-------------+---------+-----------+
           1| RESEARCH|           |
           2|   SURVEY|           |
       

更新step_status列的值取決於該 step_type值是什麼值。

當step_type是’RESEARCH’ 時，只能輸入’COMPLETE’ 或’INCOMPLETE’ 的值作為step_status值。

當step_type是“SURVEY”時，只能輸入“ASSIGNED”或“NOT ASSIGNED”的值作為step_status值。

我嘗試step_status使用以下過程管理“研究”約束：

create or replace function insert_step_status_value() returns trigger as $$
   begin
       if (new.step_status != 'COMPLETE') or (new.step_status != 'INCOMPLETE')
       from phase_steps where step_type = 'RESEARCH'
       then
           raise exception 'Status value not in range for this phase step';
       end if;
       return new;
   end;
$$ language plpgsql;

create trigger check_step_status_value before update on phase_steps
for each row execute procedure insert_step_status_value();

但是，像這樣的插入

update jobs.phase_steps
set step_status_lu = 'INCOMPLETE'
where phase_step_id = 1;

給出一個錯誤：

SQL Error [P0001]: ERROR: Status value not in range for this phase step
Where: PL/pgSQL function insert_step_status_value() line 6 at RAISE

想法？

CHECK約束應該完成這項工作。比任何觸發解決方案都更簡單、更便宜、更可靠：

要僅強制執行列出的組合併仍然允許其他任何內容，您的表定義可能如下所示：

CREATE TABLE jobs.phase_steps (
 phase_step_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, step_type     text
, step_status   text
, CONSTRAINT step_status_for_step_type
    CHECK (step_type = 'RESEARCH' AND step_status IN ('COMPLETE', 'INCOMPLETE')
        OR step_type = 'SURVEY'   AND step_status IN ('ASSIGNED', 'NOT ASSIGNED'))
);

db<>在這裡擺弄

運算符優先級對我們有利，因此不需要額外的括號。不允許ans

有其他值。step_type``step_status

手冊：

定義約束有兩種方式：表約束和列約束。列約束被定義為列定義的一部分。表約束定義不綁定到特定列，它可以包含多個列。

這仍然允許null兩列中的值。如果CHECK表達式產生trueor ，則傳遞約束null。您必須定義在哪裡排除或允許這些。也許你只是想要兩列NOT NULL？

還要禁止 的任何其他值step_type：

ALTER TABLE phase_steps
 DROP CONSTRAINT step_status_for_step_type
, ADD  CONSTRAINT step_status_for_step_type
    CHECK (CASE step_type WHEN 'RESEARCH' THEN step_status IN ('COMPLETE', 'INCOMPLETE')
                          WHEN 'SURVEY'   THEN step_status IN ('ASSIGNED', 'NOT ASSIGNED')
                          ELSE false END)

step_type現在(incl. ) 的任何其他值null到達ELSE分支並進行檢查false。（但 NULL instep_status仍然通過。）

db<>在這裡擺弄

引用自：https://dba.stackexchange.com/questions/297926