MSSQL:在创建表时添加唯一约束并允许空值

Question

抱歉，可能是菜鸟问题，但我找不到答案。

CREATE TABLE SomeTable
(
Id DECIMAL NOT NULL,
UserIdentifier NVARCHAR(100) NULL,
PRIMARY KEY (Id),
????
)

如何在允许空值的列用户标识符上的创建表中添加唯一键约束？

我知道在创建表之外你可以说...其中用户标识符不为空，但在内？

谢谢！

Answer 1

假设你想要多行值为NULL，您将无法使用UNIQUE CONSTRAINT，因为NULL仍然是一个值(即使是一个未知的值)。例如：

CREATE TABLE dbo.YourTable (UserIdentifier nvarchar(100) NULL,
CONSTRAINT UC_UI UNIQUE (UserIdentifier));
GO
INSERT INTO dbo.YourTable (UserIdentifier)
VALUES(NULL);
GO
INSERT INTO dbo.YourTable (UserIdentifier)
VALUES(NULL);
GO
DROP TABLE dbo.YourTable;

请注意，第二个INSERT失败。

但是，您可以改用条件UNIQUE INDEX：

CREATE TABLE dbo.YourTable (UserIdentifier nvarchar(100) NULL);
CREATE UNIQUE NONCLUSTERED INDEX UI_UI ON dbo.YourTable(UserIdentifier) WHERE UserIdentifier IS NOT NULL;
GO
INSERT INTO dbo.YourTable (UserIdentifier)
VALUES(NULL); -- Success
GO
INSERT INTO dbo.YourTable (UserIdentifier)
VALUES(NULL); --Success
GO
INSERT INTO dbo.YourTable (UserIdentifier)
VALUES(N'Steve'); --Success
GO
INSERT INTO dbo.YourTable (UserIdentifier)
VALUES(N'Jayne'); --Success
GO
INSERT INTO dbo.YourTable (UserIdentifier)
VALUES(N'Steve'); --Fails
GO
DROP TABLE dbo.YourTable;

正如Jeroen Mostert在评论中所说，你不能创建一个唯一的索引作为创建表的一部分;它必须在单独的语句中创建。没有语法可以创建UNIQUE INDEX作为CREATE TABLE语句的一部分。

您可以在 SQL Server 2016+ 中使用以下任一语法创建此内联(最初编写此答案时未记录)：

CREATE TABLE dbo.YourTable (UserIdentifier nvarchar(100) NULL INDEX UI_UI UNIQUE WHERE UserIdentifier IS NOT NULL);
CREATE TABLE dbo.SomeTable (UserIdentifier nvarchar(100) NULL,
INDEX UI_UI UNIQUE (UserIdentifier)  WHERE UserIdentifier IS NOT NULL);

db<>小提琴 2014， db<>小提琴 2016

Answer 2

这很简单。如果要在单列上添加，则只需添加UNIQUE关键字即可在列级别应用。
但是，如果您希望这基于多列，则需要添加CONSTRAINT。欲了解更多信息，您可以找到此链接 .link

CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL UNIQUE,   --- FOR ADDING ON ONE COLUMN
FirstName varchar(255),
Age int,
CONSTRAINT UC_Person UNIQUE (ID,LastName)   --- FOR MULTIPLE COLUMNS
);

Answer 3

在 SQL Server 中使用UNIQUE关键字。

CREATE TABLE SomeTable
(
Id DECIMAL NOT NULL,
UserIdentifier NVARCHAR(100) NULL UNIQUE,
PRIMARY KEY (Id)
)

当尝试在列中再次插入相同的值时，它会抛出错误。

请在db<>fiddle上的演示中找到相同的执行