Column constraint based on values in another column

check-constraintsconstraintdatabase-designpostgresqltrigger

I have a PostgreSQL table phase_steps, with the following example rows:

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

Update values to the step_status column depend on what value the
step_type value is.
When step_type is 'RESEARCH', only values of 'COMPLETE' or 'INCOMPLETE' can be entered for step_status values.
When step_type is 'SURVEY', only values of 'ASSIGNED' or 'NOT ASSIGNED' can be entered for step_status values.

I tried to manage the 'RESEARCH' step_status constraints with this procedure:

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();

However, an insert like

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

gives an error:

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

Thoughts?

Best Answer

A CHECK constraint does the job. Simpler, cheaper and more reliable than a trigger.

To enforce the listed combinations, your table definition could look like this:

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')
      )
);

Or a simpler equivalent:

, CONSTRAINT step_status_for_step_type CHECK (
       (step_type, step_status)
   IN (('RESEARCH', 'COMPLETE')
     , ('RESEARCH', 'INCOMPLETE')
     , ('SURVEY'  , 'ASSIGNED')
     , ('SURVEY'  , 'NOT ASSIGNED')))

fiddle

The manual:

There are two ways to define constraints: table constraints and column constraints. A column constraint is defined as part of a column definition. A table constraint definition is not tied to a particular column, and it can encompass more than one column.

Operator precedence works in our favor, no additional parentheses required.
Allows no other combined values for (step_type, step_status) - except for null values. A CHECK constraint is passed if the expression evaluates to true or null.

You may want to add a NOT NULL constraint to step_type, then step_status can still be null, and step_type can be anything (but null) in this case. But if step_status has a value, the combination has to pass the check.

Or, to also disallow any other value for 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);

Now any other value for step_type (incl. null) reaches the ELSE branch and makes the check false. (But null in step_status still passes.)

fiddle

Related Question