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

mu is too short · Accepted Answer · 2014-04-30 17:07:00Z

7

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

edited Apr 30, 2014 at 17:07

answered Apr 27, 2014 at 18:21

mu is too short

436k71 gold badges863 silver badges822 bronze badges

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

4 Comments

mu is too short Over a year ago

Shame there's no implication operator, my CHECK constraints are littered with -- (P -> Q) <-> (-P | Q) comments to remind myself what the logic is really expressing.

shaunc Over a year ago

@muistooshort: you could use P::int - Q::int <= 0 :) looks a bit more like an implication. But if you really want, use create operator to create implication operator

mu is too short Over a year ago

@shaunc: I try to stay away from adding my own operators, less baggage to carry around that way. I'd probably go with -P::int + Q::int >= 0 because it is more recognizable because I've used -P | Q so many times :)

shaunc Over a year ago

Sounds good :) -- just make sure to parenthesize for operator precedence

Clodoaldo Neto · Accepted Answer · 2014-04-27 17:55:06Z

5

create table t (
    a int,
    b int,
    check (
        a is null and b is null
        or
        a is not null and b is null
        or
        a is not null and b is not null
    )
);

insert into t (a, b) values
(null, null),
(1, null),
(1, 1),
(null, 1);
ERROR:  new row for relation "t" violates check constraint "t_check"
DETAIL:  Failing row contains (null, 1).

answered Apr 27, 2014 at 17:55

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

1 Comment

d_a_n Over a year ago

Thanks for the quick response, I had been trying to check it wasn't the invalid option, rather than checking it was one of the correct options.

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related