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 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).
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".
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)) ;
精彩评论