Check if all three columns are either not null or null

Question

I have a table with 4 columns:

create table dbo.Table ( 
  Id int not null,
  A int null,
  B int null,   
  C nvarchar (4000) null
)

How can I make sure that A, B and C are all three null or all three not null?

Amphiaster

Answer 1

You can set a check constraint:

constraint [check_abc] check ( ([A] is null and [B] is null and [C] is null) or
                               ([A] is not null and [B] is not null and [C] is not null) )

Jannette

Answer 2

You might also consider factoring these related columns out to a second table in which they are declared not null and only inserting a row where they apply.

create table dbo.Table1( 
  Id int not null primary key
)


create table dbo.Table2( 
  Id int not null primary key references Table1,
  A int not null,
  B int not null,   
  C nvarchar (4000) not null
)

Abernon