A nicer way to write a CHECK CONSTRAINT that checks that exactly one value is not null
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 onvalid 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][1] wants to check that at least 1 field is not null and the `COALESCE` keyword works well in that case).


  [1]: http://stackoverflow.com/questions/3565159/sql-constraint-trigger-is-it-possible-to-write-a-constraint-that-checks-when-y "this question"
A nicer way to write a CHECK CONSTRAINT that checks that exactly one value is not null
===

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 onvalid 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][1] wants to check that at least 1 field is not null and the `COALESCE` keyword works well in that case).


  [1]: http://stackoverflow.com/questions/3565159/sql-constraint-trigger-is-it-possible-to-write-a-constraint-that-checks-when-y "this question"