CODEWITHSUNDEEP
postgresqlconstraints
d_a_n
d_a_n

Reputation: 317

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

Postgres 9.3

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

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

I'd be grateful for any help.

Thanks in advance Dan

Upvotes: 4

Views: 4363

Answers (2)

mu is too short
mu is too short

Reputation: 434585

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

Upvotes: 7

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125204

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

Upvotes: 5

Related Questions