postgres add constraint to ensure one column is not null if another column is not null

Question

Postgres 9.3

I have two integer type columns 'a' and 'b'. Valid options are:

• 'a' is null, 'b' is null
• 'a' is not null, 'b' is null
• 'a' is not null, 'b' is not null

I'm trying to add a check/constraint which prevents the invalid option:

• 'a' is null, 'b' is not null

I'd be grateful for any help.

Thanks in advance Dan

Answer 1

Looks like your condition is actually a logical implication: if a is null then b is null. From our symbolic logic course (PHIL 140 in my case), we know that:

(P -> Q) <-> (-P | Q)

where -> is implication and <-> is logical equivalence.

Applying that to our implication and SQLizing it yields:

a is not null or b is null

so a simple check(a is not null or b is null) constraint is sufficient.

Demo: http://sqlfiddle.com/#!15/bebee/1