组合UNIQUE和CHECK约束 [英] Combining the UNIQUE and CHECK constraints
问题描述
我有一个包含列ab和c的表,如果c是false,那么我只想允许插入,如果列a和b是唯一的,但是如果c是真的,则a和b不需要是唯一的。 / p>
示例:
在表中只能有一个(foo,bar,false),但没有限制多少(foo,bar,true)可以。
我尝试过像CONSTRAINT blah UNIQUE(a,b)AND CHECK(C是TRUE),但是我不知道正确的语法。 p>
I have a table with columns a b and c, and if c is false then I only want to allow insertions if columns a and b are unique, but if c is true then a and b do not need to be unique.
Example: There can only be one (foo, bar, false) in the table, but no limit on how many (foo, bar, true) there can be.
I tried something like CONSTRAINT blah UNIQUE (a,b) AND CHECK (C is TRUE) but I can't figure out the correct syntax.
Create an indexed view returning a and b with a filter
WHERE C = false
, then create a unique index on the view. This is a general approachIf you have SQL Server 2008, then create a unique filtered index instead
Stored procedure
Trigger (before or after)
这篇关于组合UNIQUE和CHECK约束的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!