exclusive arc in tsql - constraint to check one of the columns is not null
I am implementing exclusive arc pattern of resolving multiple parents for a given table. I wrote the below table level check constraint for use in SQL Server 2008. The proble with this, if there are 3 columns, the query grows so much bigger. Is there any better to do it?
check(
(Parent1Id is null AND Parent开发者_JAVA技巧2Id is not null)
OR (Parent1Id is not null AND Parent2Id is null))
How about check(COALESCE(Parent1ID, Parent2ID) IS NOT NULL)
- this makes sure at least one is set, but to do the negative check I can't think of a quick solution.
Come to think of it, how about: (warning - not much shorter)
check(
CASE WHEN Parent1ID IS NULL THEN 0 ELSE 1 END
+ CASE WHEN Parent2ID IS NULL THEN 0 ELSE 1 END
+ CASE WHEN Parent3ID IS NULL THEN 0 ELSE 1 END
+ CASE WHEN Parent4ID IS NULL THEN 0 ELSE 1 END
= 1
)
精彩评论