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

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.

Answer

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