SQL 我可以有一个“条件唯一"吗?对表的约束?
在我的职业生涯中，我遇到过几次这个问题，但我当地的同行似乎都无法回答.假设我有一个表，它有一个描述"字段，它是一个候选键，但有时用户会在过程中途停止.因此，对于可能有 25% 的记录，该值是 null，但对于所有非 NULL 的记录，它必须是唯一的.
I've had this come up a couple times in my career, and none of my local peers seems to be able to answer it. Say I have a table that has a "Description" field which is a candidate key, except that sometimes a user will stop halfway through the process. So for maybe 25% of the records this value is null, but for all that are not NULL, it must be unique.
另一个例子可能是一个表，它必须维护一个记录的多个版本"，一个位值指示哪个是活动"的.所以候选密钥"总是被填充，但可能有三个相同的版本(活动位为 0)，只有一个是活动的(活动位为 1).
Another example might be a table which must maintain multiple "versions" of a record, and a bit value indicates which one is the "active" one. So the "candidate key" is always populated, but there may be three versions that are identical (with 0 in the active bit) and only one that is active (1 in the active bit).
我有解决这些问题的替代方法(在第一种情况下，在存储过程或业务层中强制执行规则代码，在第二种情况下，使用触发器填充存档表并在需要时联合表历史).我不想要替代方案(除非有明显更好的解决方案)，我只是想知道是否有任何风格的 SQL 可以以这种方式表达条件唯一性".我正在使用 MS SQL，所以如果有办法做到这一点，那就太好了.我主要只是对这个问题在学术上感兴趣.
I have alternate methods to solve these problems (in the first case, enforce the rule code, either in the stored procedure or business layer, and in the second, populate an archive table with a trigger and UNION the tables when I need a history). I don't want alternatives (unless there are demonstrably better solutions), I'm just wondering if any flavor of SQL can express "conditional uniqueness" in this way. I'm using MS SQL, so if there's a way to do it in that, great. I'm mostly just academically interested in the problem.
推荐答案
如果您使用的是 SQL Server 2008，索引过滤器可能是您的解决方案:
If you are using SQL Server 2008 a Index filter would maybe your solution:
http://msdn.microsoft.com/en-us/library/ms188783.aspx
这就是我如何使用多个 NULL 值强制执行唯一索引
This is how I enforce a Unique Index with multiple NULL values
CREATE UNIQUE INDEX [IDX_Blah] ON [tblBlah] ([MyCol]) WHERE [MyCol] IS NOT NULL
相关文章