Prohibit users from updating a column if another column is null?

Question

I have mytable which has 3 integer fields: id, status, project_id.

I've told people that they should not progress status past 4 before assigning it a project_id value. Naturally people don't listen and then there are problems down the road.

Is there a way to return an error if someone tries to update from status 4 to 5 while project_id column is null? I still need people to be able to update status from 2 or 3 to status 4 regardless of it having a project_id.

score 5 · Accepted Answer · answered Jan 21 '19 at 12:31

You can use CHECK constraint as suggested by @stickbit if you need very simple checks.

If you need a more complicated logic, you can use TRIGGER functionality

CREATE FUNCTION check_status()
  RETURNS trigger AS
$mytrigger$
BEGIN
   IF OLD.status = 4 AND NEW.status >= 5 AND NEW.project_id IS NULL THEN
      RAISE EXCEPTION 'Project ID must be assigned before progressing to status 5';
   END IF;
   RETURN NEW;
END
$mytrigger$
LANGUAGE plpgsql;

CREATE TRIGGER project_id_check
BEFORE UPDATE ON "MyTable"
FOR EACH ROW EXECUTE PROCEDURE check_status();

score 2 · Answer 2 · answered Jan 21 '19 at 12:27

2

How about a check constraint on the table:

CHECK (project_id IS NOT NULL OR status < 5)

answered Jan 21 '19 at 12:27

sticky bit

36,626
12
31
42

This seems like the way to go if the data wasn't already messed up. It says that rows are violating the check. I'll go with the below solution, thanks – Luffydude Jan 21 '19 at 12:41
1

@Luffydude If the data is already messed up, you really should clean your data. Put the new constraints/triggers in first, then go back and clean the data. – Aleks G Jan 21 '19 at 20:14
@AleksG if you have time to clean 1k rows then let me know, people will fix their data eventually. At the moment the NOT VALID suggested below is enough to make your query work – Luffydude Jan 22 '19 at 09:41

Erwin Brandstetter · Answer 3 · 2019-01-21T17:44:12.873

If you have data violating your desired rules, you can still use a CHECK constraint like demonstrated by sticky bit. Just make it NOT VALID:

ALTER TABLE mytable ADD CONSTRAINT project_id_required_for_status_5_or_higher
CHECK project_id IS NOT NULL OR status < 5) NOT VALID;

Then the constraint is only applied to subsequent inserts and updates. Existing rows are ignored. (But any new update must fix violating values or it will fail.)

You should also have a FOREIGN KEY constraint enforcing referential integrity for project_id, else the constraint can easily be circumvented with dummy values.

Fine point: a CHECK constraint not only prohibits the updates you mentioned, but inserts with a violating state as well.

Once all rows are fixed to comply with the new rule, you can VALIDATE the constraint:

ALTER TABLE mytable VALIDATE CONSTRAINT project_id_required_for_status_5_or_higher;

More:

Trigger vs. check constraint

Prohibit users from updating a column if another column is null?

3 Answers3