SQL可以具有“条件唯一"的条件吗?表上的约束? [英] SQL can I have a "conditionally unique" constraint on a table?

问题描述

我在职业生涯中遇到过几次，而我当地的同行似乎都无法回答.假设我有一个表，表中有一个描述"字段，这是一个候选键，只是有时用户会在过程中途停下来.因此，对于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).

我有其他方法可以解决这些问题(在第一种情况下，在存储过程或业务层中强制执行规则代码，在第二种情况下，用触发器填充存档表，并在需要时使用UNION表历史).我不希望有替代方法(除非有明显更好的解决方案)，我只是想知道是否有任何一种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

这篇关于SQL可以具有“条件唯一"的条件吗?表上的约束?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！