A nicer way to write a CHECK CONSTRAINT that checks that exactly one value is not null

Question

Imagine that I have a table with integer columns Col1, Col2, Col3, Col4. Each column is nullable and a valid row must contain a value in exactly 1 columns (i.e. all nulls is invalid and more than 1 column is also invalid).

At the moment I have a check constraint like this

ALTER TABLE [dbo].[MyTable]  WITH CHECK 
    ADD CONSTRAINT [CK_ReportTemplateAttributes] CHECK  
    ((
        [Col1] IS NOT NULL AND [Col2] IS NULL AND [Col3] IS NULL AND [Col4] IS NULL
        OR 
        [Col1] IS NULL AND [Col2] IS NOT NULL AND [Col3] IS NULL AND [Col4] IS NULL
        OR 
        [Col1] IS NULL AND [Col2] IS NULL AND [Col3] IS NOT NULL AND [Col4] IS NULL
        OR 
        [Col1] IS NULL AND [Col2] IS NULL AND [Col3] IS NULL AND [Col4] IS NOT NULL
    ));
GO;

It works but it strikes me that there might be a more elegant way to achieve the same result (for example this questioner wants to check that at least 1 field is not null and the COALESCE keyword works well in that case).

Tonga

For me, your constraint is clearer than the accepted answer. Either way, I think there is a smell of a design problem: table not fully normalized, perhaps? Perhaps real column names would give more of a clue (hoping those aren't the real names!) – Requisition

Answer 1

The most concise way I can think of at the moment is.

ALTER TABLE [dbo].[MyTable]  WITH CHECK 
ADD CONSTRAINT [CK_ReportTemplateAttributes] CHECK  
(3 = ISNULL([Col1] - [Col1],1) + 
     ISNULL([Col2] - [Col2],1) + 
     ISNULL([Col3] - [Col3],1) + 
     ISNULL([Col4] - [Col4],1)) ;

Climatology

Personally I prefer this check constraint over the (verbose) check that I posted but having just done a straw poll of other developers in my group, the resounding answer was that no one was certain what the behaviour of the ISNULL method would be - which surprised me. – Tonga

Answer 2

To riff on the other answer here, I think this is a little more self-documenting:

ALTER TABLE [dbo].[MyTable]  WITH CHECK 
ADD CONSTRAINT [CK_ReportTemplateAttributes] CHECK  
(1 = CASE when [Col1] IS NULL THEN 0 ELSE 1 END + 
     CASE when [Col2] IS NULL THEN 0 ELSE 1 END + 
     CASE when [Col3] IS NULL THEN 0 ELSE 1 END + 
     CASE when [Col4] IS NULL THEN 0 ELSE 1 END ) ;

It also has the benefit of avoiding the bug where you alter the constraint to take another column into consideration but forget to update the "3" to "[number of columns in constraint] - 1".

Embassy

I like your way too Ben (although you need to change "IS NULL" to "IS NOT NULL"). Personally I prefer Martin's answer but other members of my team disagree. – Tonga

If they disagree, ask them to do the case for 10 columns where 2 of them can be non-null. This style of approach scales well in the development effort. The other does not. Also, my answer has been edited for the requirements. – Embassy