Geek Answers Handbook

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

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

I currently have a control limit 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;

This works, but it seems to me that there may be a more elegant way to achieve the same result (for example, this questionnaire wants to check that at least 1 field is not zero, and the keyword COALESCEworks well in this case).

+5
2

, , - .

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

, , :

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

, , , "3" "[ ] - 1".

+9

More articles:


All Articles