是否可以加唯一约束 有空_sql server - 如何创建一个也允许空值的唯一约束？

sql server - 如何创建一个也允许空值的唯一约束？

我希望对我将使用GUID填充的列具有唯一约束。 但是，我的数据包含此列的空值。 如何创建允许多个空值的约束？

这是一个示例场景。 考虑这个架构：

CREATE TABLE People (

Id INT CONSTRAINT PK_MyTable PRIMARY KEY IDENTITY,

Name NVARCHAR(250) NOT NULL,

LibraryCardId UNIQUEIDENTIFIER NULL,

CONSTRAINT UQ_People_LibraryCardId UNIQUE (LibraryCardId)

)

然后看看我正在尝试实现的代码：

-- This works fine:

INSERT INTO People (Name, LibraryCardId)

VALUES ('John Doe', 'AAAAAAAA-AAAA-AAAA-AAAA-AAAAAAAAAAAA');

-- This also works fine, obviously:

INSERT INTO People (Name, LibraryCardId)

VALUES ('Marie Doe', 'BBBBBBBB-BBBB-BBBB-BBBB-BBBBBBBBBBBB');

-- This would *correctly* fail:

--INSERT INTO People (Name, LibraryCardId)

--VALUES ('John Doe the Second', 'AAAAAAAA-AAAA-AAAA-AAAA-AAAAAAAAAAAA');

-- This works fine this one first time:

INSERT INTO People (Name, LibraryCardId)

VALUES ('Richard Roe', NULL);

-- THE PROBLEM: This fails even though I'd like to be able to do this:

INSERT INTO People (Name, LibraryCardId)

VALUES ('Marcus Roe', NULL);

最终语句失败并显示一条消息：

违反UNIQUE KEY约束'UQ_People_LibraryCardId'。 无法在对象'dbo.People'中插入重复键。

如何更改我的架构和/或唯一性约束，以便它允许多个NULL值，同时仍检查实际数据的唯一性？

Stuart asked 2019-01-22T07:33:44Z

14个解决方案

1154 votes

您正在寻找的确实是ANSI标准SQL：92，SQL：1999和SQL：2003的一部分，即UNIQUE约束必须禁止重复的非NULL值但接受多个NULL值。

但是，在SQL Server的Microsoft世界中，允许单个NULL，但是多个NULL不是......

在SQL Server 2008中，您可以基于排除NULL的谓词定义唯一的筛选索引：

CREATE UNIQUE NONCLUSTERED INDEX idx_yourcolumn_notnull

ON YourTable(yourcolumn)

WHERE yourcolumn IS NOT NULL;

在早期版本中，您可以使用带有NOT NULL谓词的VIEWS来强制执行约束。

Vincent Buck answered 2019-01-22T07:35:02Z

117 votes

SQL Server 2008 +

您可以使用WHERE子句创建一个接受多个NULL的唯一索引。 请参阅以下答案。

在SQL Server 2008之前

您不能创建UNIQUE约束并允许NULL。 您需要设置NEWID()的默认值。

在创建UNIQUE约束之前，将现有值更新为NEWID()，其中为NULL。

Jose Basilio answered 2019-01-22T07:34:18Z

27 votes

SQL Server 2008及以上版本

只需过滤一个唯一索引：

CREATE UNIQUE NONCLUSTERED INDEX UQ_Party_SamAccountName

ON dbo.Party(SamAccountName)

WHERE SamAccountName IS NOT NULL;

在较低版本中，仍然不需要物化视图

对于SQL Server 2005及更早版本，您可以在没有视图的情况下执行此操作。 我刚刚添加了一个独特的约束，就像你要求我的一张桌子一样。 鉴于我想要SamAccountName列中的唯一性，但我想允许多个NULL，我使用了物化列而不是物化视图：

ALTER TABLE dbo.Party ADD SamAccountNameUnique

AS (Coalesce(SamAccountName, Convert(varchar(11), PartyID)))

ALTER TABLE dbo.Party ADD CONSTRAINT UQ_Party_SamAccountName

UNIQUE (SamAccountNameUnique)

您只需在计算列中放置一些内容，当实际所需的唯一列为NULL时，该列将在整个表中保证唯一。 在这种情况下，UNIQUE是一个标识列，数字将永远不会匹配任何Issue，所以它对我有用。 您可以尝试自己的方法 - 确保您了解数据的域，以便不可能与实际数据交叉。 这可以像预先设置这样的区分字符一样简单：

Coalesce('n' + SamAccountName, 'p' + Convert(varchar(11), PartyID))

即使UNIQUE有一天变得非数字并且可能与Issue重合，现在也没关系。

请注意，包含计算列的索引的存在会隐式地使每个表达式结果与表中的其他数据一起保存到磁盘，这会占用额外的磁盘空间。

请注意，如果您不想要索引，则仍可以通过将关键字UNIQUE添加到列表达式定义的末尾，将表达式预先计算到磁盘来节省CPU。

在SQL Server 2008及更高版本中，如果可能，请务必使用已过滤的解决方案！

争议

请注意，一些数据库专业人员会将此视为“代理NULL”的情况，这肯定存在问题(主要是由于试图确定何时某些内容是真实值或缺失数据的代理值的问题;也可能存在问题 与非NULL代理值的数量相乘疯狂)。

但是，我认为这种情况有所不同。 我正在添加的计算列永远不会用于确定任何内容。 它没有任何意义，并且没有编码在其他正确定义的列中未单独找到的信息。 永远不应该选择或使用它。

所以，我的故事是，这不是代理NULL，我坚持它！ 由于除了欺骗UNIQUE索引以忽略NULL之外，我们实际上并不希望将非NULL值用于任何目的，因此我们的用例没有出现正常代理NULL创建的问题。

所有这一切，我没有使用索引视图的问题 - 但它带来了一些问题，如使用UNIQUE的要求。有乐趣添加一个新列到您的基表(你至少必须放弃 index，然后删除视图或将视图更改为不受架构限制)。 请参阅在SQL Server(2005)(以及更高版本)，(2000)中创建索引视图的完整(长)要求列表。

更新

如果您的列是数字，则可能存在确保使用UNIQUE的唯一约束不会导致冲突的挑战。 在这种情况下，有一些选择。 一种可能是使用负数，将“代理空值”仅设置在负范围内，将“实际值”仅设置在正范围内。 或者，可以使用以下模式。 在表Issue(其中IssueID是PRIMARY KEY)，可能有也可能没有TicketID，但如果有，则它必须是唯一的。

ALTER TABLE dbo.Issue ADD TicketUnique

AS (CASE WHEN TicketID IS NULL THEN IssueID END);

ALTER TABLE dbo.Issue ADD CONSTRAINT UQ_Issue_Ticket_AllowNull

UNIQUE (TicketID, TicketUnique);

如果IssueID 1具有票证123，则UNIQUE约束将在值(123，NULL)上。 如果IssueID 2没有票证，则它将打开(NULL，2)。 有些人认为这个约束不能复制到表中的任何行，并且仍然允许多个NULL。

ErikE answered 2019-01-22T07:37:10Z

15 votes

对于使用Microsoft SQL Server管理器且想要创建唯一但可以为空的索引的人，您可以像通常在新索引的索引属性中那样创建唯一索引，从左侧面板中选择“过滤器”，然后输入 你的过滤器(这是你的where子句)。 它应该是这样的：

([YourColumnName] IS NOT NULL)

这适用于MSSQL 2012

Howard answered 2019-01-22T07:37:39Z

9 votes

当我应用下面的唯一索引时：

CREATE UNIQUE NONCLUSTERED INDEX idx_badgeid_notnull

ON employee(badgeid)

WHERE badgeid IS NOT NULL;

每个非null更新和插入失败，错误如下：

UPDATE失败，因为以下SET选项具有不正确的设置：'ARITHABORT'。

我在MSDN上找到了这个

在计算列或索引视图上创建或更改索引时，SET ARITHABORT必须为ON。 如果SET ARITHABORT为OFF，则具有计算列或索引视图索引的表上的CREATE，UPDATE，INSERT和DELETE语句将失败。

所以为了让这个工作正常，我做到了这一点

右键单击[数据库] - ＆gt;属性 - ＆gt;选项 - ＆gt;其他   选项 - ＆gt;杂项 - ＆gt;启用算术中止 - ＆gt; true

我相信可以在代码中使用设置此选项

ALTER DATABASE "DBNAME" SET ARITHABORT ON

但我没有测试过这个

Michael Taylor answered 2019-01-22T07:38:54Z

6 votes

创建一个仅选择非INSTEAD OF列的视图，并在视图上创建UPDATE：

CREATE VIEW myview

AS

SELECT *

FROM mytable

WHERE mycolumn IS NOT NULL

CREATE UNIQUE INDEX ux_myview_mycolumn ON myview (mycolumn)

请注意，您需要在视图而不是表格上执行INSTEAD OF和UPDATE。

您可以使用INSTEAD OF触发器执行此操作：

CREATE TRIGGER trg_mytable_insert ON mytable

INSTEAD OF INSERT

AS

BEGIN

INSERT

INTO myview

SELECT *

FROM inserted

END

Quassnoi answered 2019-01-22T07:39:30Z

4 votes

可以在聚簇索引视图上创建唯一约束

您可以像这样创建视图：

CREATE VIEW dbo.VIEW_OfYourTable WITH SCHEMABINDING AS

SELECT YourUniqueColumnWithNullValues FROM dbo.YourTable

WHERE YourUniqueColumnWithNullValues IS NOT NULL;

和这样的独特约束：

CREATE UNIQUE CLUSTERED INDEX UIX_VIEW_OFYOURTABLE

ON dbo.VIEW_OfYourTable(YourUniqueColumnWithNullValues)

Lieven Keersmaekers answered 2019-01-22T07:40:05Z

4 votes

它也可以在设计师中完成

右键单击索引＆gt; 获取此窗口的属性

Yonatan Tuchinsky answered 2019-01-22T07:40:33Z

2 votes

也许考虑一个“INSTEAD OF”触发器并自己检查？ 使用列上的非聚集(非唯一)索引来启用查找。

Marc Gravell answered 2019-01-22T07:40:55Z

1 votes

如前所述，当涉及到UNIQUE CONSTRAINT时，SQL Server没有实现ANSI标准。自2007年以来，Microsoft Connect就此提供了一个票据。正如那里所建议的那样，今天最好的选择是使用过滤索引。 在另一个答案或计算列中，例如：

CREATE TABLE [Orders] (

[OrderId] INT IDENTITY(1,1) NOT NULL,

[TrackingId] varchar(11) NULL,

...

[ComputedUniqueTrackingId] AS (

CASE WHEN [TrackingId] IS NULL

THEN '#' + cast([OrderId] as varchar(12))

ELSE [TrackingId_Unique] END

),

CONSTRAINT [UQ_TrackingId] UNIQUE ([ComputedUniqueTrackingId])

)

Baris Akar answered 2019-01-22T07:41:18Z

0 votes

您可以创建一个INSTEAD OF触发器来检查特定条件和错误(如果满足)。 在较大的表上创建索引可能成本很高。

这是一个例子：

CREATE TRIGGER PONY.trg_pony_unique_name ON PONY.tbl_pony

INSTEAD OF INSERT, UPDATE

AS

BEGIN

IF EXISTS(

SELECT TOP (1) 1

FROM inserted i

GROUP BY i.pony_name

HAVING COUNT(1) > 1

)

OR EXISTS(

SELECT TOP (1) 1

FROM PONY.tbl_pony t

INNER JOIN inserted i

ON i.pony_name = t.pony_name

)

THROW 911911, 'A pony must have a name as unique as s/he is. --PAS', 16;

ELSE

INSERT INTO PONY.tbl_pony (pony_name, stable_id, pet_human_id)

SELECT pony_name, stable_id, pet_human_id

FROM inserted

END

Paul answered 2019-01-22T07:41:41Z

-1 votes

您不能使用UNIQUE约束执行此操作，但可以在触发器中执行此操作。

CREATE TRIGGER [dbo].[OnInsertMyTableTrigger]

ON [dbo].[MyTable]

INSTEAD OF INSERT

AS

BEGIN

SET NOCOUNT ON;

DECLARE @Column1 INT;

DECLARE @Column2 INT; -- allow nulls on this column

SELECT @Column1=Column1, @Column2=Column2 FROM inserted;

-- Check if an existing record already exists, if not allow the insert.

IF NOT EXISTS(SELECT * FROM dbo.MyTable WHERE Column1=@Column1 AND Column2=@Column2 @Column2 IS NOT NULL)

BEGIN

INSERT INTO dbo.MyTable (Column1, Column2)

SELECT @Column2, @Column2;

END

ELSE

BEGIN

RAISERROR('The unique constraint applies on Column1 %d, AND Column2 %d, unless Column2 is NULL.', 16, 1, @Column1, @Column2);

ROLLBACK TRANSACTION;

END

END

Michael Brown answered 2019-01-22T07:42:03Z

-1 votes

CREATE UNIQUE NONCLUSTERED INDEX [UIX_COLUMN_NAME]

ON [dbo].[Employee]([Username] ASC) WHERE ([Username] IS NOT NULL)

WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF,

DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF,

MAXDOP = 0) ON [PRIMARY];

user5536124 answered 2019-01-22T07:42:19Z

-1 votes

这段代码如果你用textBox创建一个注册表单并使用insert和你的textBox是空的，你点击提交按钮。

CREATE UNIQUE NONCLUSTERED INDEX [IX_tableName_Column]

ON [dbo].[tableName]([columnName] ASC) WHERE [columnName] !=`''`;

Ahmed Soliman Flasha answered 2019-01-22T07:42:41Z