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).

Answer 1

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".

Answer 2

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)) ;