SQL Server唯一约束

在本教程中，将学习如何使用SQL Server UNIQUE约束来确保列或列组中包含的数据的唯一性。

SQL Server UNIQUE约束简介

SQL Server UNIQUE约束用于确保存储在列或列组中的数据在表中的行中是唯一的。

以下语句创建一个表：hr.persons，其email邮件列中的数据在表的行中是唯一的：

CREATE SCHEMA hr;
GO

CREATE TABLE hr.persons(
    person_id INT IDENTITY PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE
);

在此语法中，将UNIQUE约束定义为列约束。 还可以将UNIQUE约束定义为表约束：

CREATE TABLE hr.persons(
    person_id INT IDENTITY PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    UNIQUE(email)
);

SQL Server自动创建UNIQUE索引以强制存储在参与UNIQUE约束的列中的数据的唯一性。 因此，如果尝试插入重复行，SQL Server将拒绝更改并返回一条错误消息，提示说已违反UNIQUE约束。

以下语句在hr.persons表中插入一个新行：

INSERT INTO hr.persons(first_name, last_name, email)
VALUES('Max','Su','maxsu@xuhuhu.com');

上面语句按预期工作。 由于email具有UNIQUE约束，同一表中email列的值不能相同，所以下面插入语句会失败：

INSERT INTO hr.persons(first_name, last_name, email)
VALUES('Max2','Su2','maxsu@xuhuhu.com');

SQL Server发出类似下面错误消息：

Violation of UNIQUE KEY constraint 'UQ__persons__XAXCDXXXXXE8240E4E'. Cannot insert duplicate key in object 'hr.persons'. The duplicate key value is (maxsu@xuhuhu.com).

如果没有为UNIQUE约束指定名称，SQL Server将自动为其生成名称。 在此示例中，自动生成的约束名称为：UQ__persons__XAXCDXXXXXE8240E4E，是不是有点不太可读？

要为UNIQUE约束指定特定名称，请使用CONSTRAINT关键字，如下所示：

CREATE TABLE hr.persons (
    person_id INT IDENTITY PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    CONSTRAINT unique_email UNIQUE(email)
);

上面语句中，指定了UNIQUE约束名称为：unique_email。

为UNIQUE约束指定名称的好处是：

• 更容易对错误消息进行分类。
• 修改约束时，可以引用此约束的名称。

UNIQUE约束与PRIMARY KEY约束

尽管UNIQUE和PRIMARY KEY约束都强制数据的唯一性，但是当要强制实现不是主键列的列或列组的唯一性时，应使用UNIQUE约束而不是PRIMARY KEY约束。

与PRIMARY KEY约束不同，UNIQUE约束允许NULL值。 此外，UNIQUE约束将NULL视为常规值，因此，它只允许每列一个NULL。

以下语句插入一行，其email列中的值为NULL：

INSERT INTO hr.persons(first_name, last_name)
VALUES('zaixian','Su');

现在，如果尝试在email列中再插入一个NULL，则会收到错误消息：

INSERT INTO hr.persons(first_name, last_name)
VALUES('Lily','Lee');

执行上面插入语句，将会提示以下错误：

Violation of UNIQUE KEY constraint 'UQ__persons__AB382E6S7DF0E9W'. Cannot insert duplicate key in object 'hr.persons'. The duplicate key value is (<NULL>).

具有多列的UNIQUE约束

要为一组列定义UNIQUE约束，可以将其写为表约束，列名以逗号分隔，如下所示：

CREATE TABLE table_name (
    key_column data_type PRIMARY KEY,
    column1 data_type,
    column2 data_type,
    column3 data_type,
    ...,
    UNIQUE (column1,column2，...column_n)
);

以下示例创建一个UNIQUE约束，此约束包含两列：person_id和skill_id：

CREATE TABLE hr.person_skills (
    id INT IDENTITY PRIMARY KEY,
    person_id int,
    skill_id int,
    updated_at DATETIME,
    UNIQUE (person_id, skill_id)
);

将UNIQUE约束添加到列

将UNIQUE约束添加到表中的现有列或一组列时，SQL Server首先检查这些列中的现有数据，以确保所有值都是唯一的。 如果SQL Server找到重复值，则它将返回错误，并且不会执行添加UNIQUE约束。

以下显示了向表中添加UNIQUE约束的语法：

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
UNIQUE(column1, column2,...);

假设有以下hr.persons表：

CREATE TABLE hr.persons (
    person_id INT IDENTITY PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    phone VARCHAR(20),
);

以下语句向email列添加UNIQUE约束：

ALTER TABLE hr.persons
ADD CONSTRAINT unique_email UNIQUE(email);

类似地，以下语句将向phone列添加UNIQUE约束：

ALTER TABLE hr.persons
ADD CONSTRAINT unique_phone UNIQUE(phone);

删除UNIQUE约束

要删除UNIQUE约束，请使用ALTER TABLE DROP CONSTRAINT语句，如下所示：

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

以下语句用于从hr.person表中删除名称为：unique_phone的约束：

ALTER TABLE hr.persons
DROP CONSTRAINT unique_phone;

修改UNIQUE约束

SQL Server没有任何直接语句来修改UNIQUE约束，因此，如果要更改约束，则需要先删除约束并重新创建约束。

在本教程中，学习了如何使用SQL Server UNIQUE约束来确保列或列组中包含的数据是唯一的。