开发者

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

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜