Postgresql – Only one non-nullable value across 3 columns

postgresql

I have 3 "Nullable" columns.

I am looking for a way to restrain it so that:

  1. One of the three has value, the other two columns must be null
  2. All three cannot be null

Is it possible to do this?

I tried this:

CREATE UNIQUE INDEX alpha ON contacts ("a")
WHERE "b" IS NULL AND "c" IS NULL;

CREATE UNIQUE INDEX beta ON contacts ("b")
WHERE "a" IS NULL AND "c" IS NULL;

CREATE UNIQUE INDEX gamma ON contacts ("c")
WHERE "a" IS NULL AND "b" IS NULL;

Best Answer

That's not what indexes are for, you need a check constraint for that,

For the "at most one can be not null" you can use a little trick: casting a boolean expression to an integer yields 0 or 1. If you add up the values for a is not null condition, the rule "at most one can be not null" translates to "the sum of those must always be 1":

alter table contacts add constraint only_one_not_null
   check (   (a is not null)::int 
           + (b is not null)::int 
           + (c is not null)::int = 1);

The above can be simplified by using the num_nonnulls() function:

alter table contacts add constraint only_one_not_null 
   check (num_nonnulls(a,b,c) = 1);

This also covers the "not all three can be null" requirement.

Related Question