CODEWITHSUNDEEP
sql-servert-sqlcheck-constraints
Pomme
Pomme

Reputation: 87

Some columns should all be filled or all should be nulls

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?

Upvotes: 0

Views: 124

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269553

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.

Upvotes: 2

Related Questions