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

Question

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:

Name, Code, Reason can all be null simultaneously
If both Name and Code has a value then Reason must be null
If Reason has a value, then both Name and Code must be null

Thank you in advance.

As a side note you should normalize this if you can. This is not a sound schema nor practice. When you start asking yourself these kinds of question you should consider tweaking your design (which is easy in this case). — Evan Carroll
– Evan Carroll, Commented Aug 31, 2018 at 18:54

Lukasz Szozda · Accepted Answer · 2018-08-31 18:57:48Z

6

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

edited Aug 31, 2018 at 18:57

answered Aug 31, 2018 at 17:35

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Andrew Lazarus Over a year ago

I believe OR((ProjectName IS NOT NULL AND ProjectCode IS NOT NULL) AND DenialReason IS NULL) should be OR((ProjectName IS NOT NULL AND ProjectCode IS NOT NULL) AND DenialReason IS NULL)

Lukasz Szozda Over a year ago

@AndrewLazarus Please check my db fiddle demo. I treat 2) if Name/Code has a value then Reason must be null, as OR and it works OK. I would be greatful if you could provide counterexample.

Andrew Lazarus Over a year ago

My reading of the OP's rule is that if DenialReason IS NULL, both ProjectName and ProjectCode must be filled in. INSERT INTO ProjectCreationTasks VALUES (5, 'PName', NULL, NULL); succeeds (added to your DBFiddle) but should fail.

mkhira2 Over a year ago

@AndrewLazarus your reading is correct. If name has a value, so must code. Likewise if code has a value, so must name.

Andrew Lazarus Over a year ago

The enumerated reasons were indeed very ambiguous. My clue was the narrative: "To approve, the admin sets both a ProjectName and ProjectCode;" (emphasis mine)

|

Andrew Lazarus · Accepted Answer · 2018-08-31 18:35:22Z

2

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

answered Aug 31, 2018 at 18:35

Andrew Lazarus

19.5k3 gold badges38 silver badges57 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related