创建唯一索引一个/唯一/单个NULL
似乎在SQL Server中,唯一索引将NULL视为“只是另一个值”,而不是像其余的SQL那样,其中与NULL的比较返回NULL。创建唯一索引一个/唯一/单个NULL
假设你有一个唯一索引的表(t
)在空列K
:
K V
0 32
1 12
3 45
都很好。
但它也将允许
K V
0 32
1 12
3 45
NULL 89 <-- Baaad
反之亦然,这也将允许下列:
K V
NULL 89
0 32 <-- not good
我可以看到这是可能是一个潜在的灾难,因为我使用NULL值关键值代表没有进一步分解的值 - 总计和分解导致重复计数或不一致。
我可以找到貌似成千上万的问题,其中人们想做相反的事情(允许多个NULL),但没有人想将NULL视为NULL。
我怎样才能获得SQL Server将空值作为空值(和只允许一个NULL 或在一列任意数量的唯一值)唯一索引?
如果你想要什么Andomar的解释是正确的,它可能是可行的如果你有一个已经包含了所有可能的K
值的表:
create table dbo.T (
K int null,
V int not null,
)
go
create table dbo.PossibleKs (
K int not null
)
insert into dbo.PossibleKs (K) values (0),(1),(2)
go
create view dbo.TV
with schemabinding
as
select pk.K
from
dbo.T t
inner join
dbo.PossibleKs pk
on
t.K = pk.K or
t.K is null
GO
create unique clustered index IX_TV on dbo.TV (K)
而且你的测试用例:
insert into dbo.T(K,V) values
(0, 32),
(1, 12),
(3, 45)
go
insert into dbo.T(K,V) values
(NULL,89)
--Msg 2601, Level 14, State 1, Line 1
--Cannot insert duplicate key row in object 'dbo.TV' with unique index 'IX_TV'. The duplicate key value is (0).
--The statement has been terminated.
go
delete from dbo.T
go
insert into dbo.T(K,V) values
(NULL,89)
go
insert into dbo.T(K,V) values
(0, 32)
--Msg 2601, Level 14, State 1, Line 1
--Cannot insert duplicate key row in object 'dbo.TV' with unique index 'IX_TV'. The duplicate key value is (0).
--The statement has been terminated.
非常有趣的创意使用索引视图。这看起来像会起作用。谢谢! – Fowl 2013-05-01 10:17:07
所以你想要一个null
或任何数量的唯一号码。我不认为这可以可靠地使用约束来强制执行。
您可以使用触发器。触发器将不得不回答如下问题:您是否正在更新行至null
?是否已经有一排是null
?你是否正在更新已经是null
的行?这个触发器很复杂,很难维护。
您可以使用存储过程操纵表。存储过程可以在事务中执行更新/插入/删除操作。在提交之前,他们可以检查表格是由一个null
还是任意数量的其他值组成。你可以合理地保持这一点。
在一天结束时,您的设计会施加难以实现的异常约束。也许你可以重新审视这个设计。
是的触发器是不理想的。 :)我认为我的设计实际上并不是那么不寻常,它只是消除了一点灵活性,这会大大增加我的查询在所有情况下正确处理的复杂性。这种方式可以保证“简单”的查询正常工作。 – Fowl 2013-05-01 10:18:04
我不清楚你实际上在寻找什么样的行为。您使用短语“将NULLs视为NULL”,就像它应该是显而易见的一样,只是从这个短语中,但对我而言,事实并非如此。我也发现你的第二个例子不清楚。 – 2013-05-01 06:23:16
SQL-Server允许在具有唯一约束的列中最多有一个“NULL”。其他DBMS(在这种情况下与标准一起)允许多个空值。您可以通过制作唯一的部分索引来绕过这一点,但我不认为这是您的目标。目前还不清楚你的目标是什么。 – 2013-05-01 06:26:28
在我的脑海里,任何与NULL的比较都应该产生一个NULL,因此如果一个列只包含一个NULL,则所有与其他值(包括NULL)的比较都会失败。我可以看到“乐观”可能是有用的,并且假设“NULL”代表独特的未知数,但在我的情况下,它会是p私的并确保数据库的完整性。 – Fowl 2013-05-01 10:10:30