java 创建唯一性约束_如何创建一个也允许空值的唯一约束？

我想在要用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值，同时仍检查实际数据的唯一性？

#1楼

您不能使用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

#2楼

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

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语句将失败。

因此，为了使其正常工作，我这样做了

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

我相信可以在代码中使用

ALTER DATABASE "DBNAME" SET ARITHABORT ON

但我还没有测试

#3楼

对于正在使用Microsoft SQL Server Manager并想要创建唯一但可为空的索引的用户，您可以像通常那样创建唯一索引，然后在新索引的索引属性中，从左侧面板中选择“过滤器”，然后输入您的过滤器(这是您的where子句)。 它应显示如下内容：

([YourColumnName] IS NOT NULL)

这适用于MSSQL 2012

#4楼

如前所述，SQL Server在UNIQUE CONSTRAINT方面未实现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])

)

#5楼

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];