Add PostgreSQL column constraint based on value in other column(s)

user_7021665 0
Viewed 2007

I have a table:

CREATE TABLE ProjectCreationTasks 
(
    Id           text NOT NULL PRIMARY KEY,
    ProjectName  text,
    ProjectCode  text,
    DenialReason text
);

An administrator can approve or deny a project creation request. To approve, the admin sets both a ProjectName and ProjectCode; to deny, the admin sets a DenialReason.

How can I add a constraint such that:

  1. Name, Code, Reason can all be null simultaneously

  2. If both Name and Code has a value then Reason must be null

  3. If Reason has a value, then both Name and Code must be null

Thank you in advance.

postgresql sql
2 Answers

You could use CHECK constaint to implement this kind of logic:

CREATE TABLE ProjectCreationTasks ( 
Id                  text NOT NULL PRIMARY KEY,
ProjectName         text,
ProjectCode         text,
DenialReason        text,
CONSTRAINT my_constraint CHECK
((ProjectName IS NULL AND ProjectCode IS NULL AND DenialReason IS NULL)
 OR(ProjectName IS NOT NULL AND ProjectCode IS NOT NULL AND DenialReason IS NULL)
 OR(DenialReason IS NOT NULL AND ProjectName IS NULL AND ProjectCode IS NULL))
);

DBFiddle Demo

user_50708790

The answer from Lukasz Szozda is correct as modified in my comment (the problem statement is slightly ambiguous). A slightly shorter equivalent clause that might be harder to read is

CONSTRAINT my_constraint CHECK
((ProjectName IS NULL = ProjectCode IS NULL) -- go together
    AND (ProjectCode IS NULL OR DenialReason IS NULL) -- allow both NULL but disallow both NOT NULL
);
user_7178980
Related