Some columns should all be filled or all should be nulls

Question

My table (10 columns) has 4 columns (A, B, C, D) that should all be either null or all be filled.

I try doing it the following way:

constraint chk_same check (A is not null AND B is not null And C is not null AND is not null) OR (A is null AND B is null And C is null AND D is null)

It looks bad, is there a better/easier way to do it?

Answer 1

Your method is fine. A more general approach is to count the number of NULL values and check that:

constraint chk_same
    check ( ((case when A is null then 1 else 0 end) +
             (case when B is null then 1 else 0 end) +
             (case when C is null then 1 else 0 end) +
             (case when D is null then 1 else 0 end)
            ) in (0, 4)
          ) ;

This is more general because you can readily check if 2 out of 4 or 3 out of 4 columns have NULL values.