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約束來確保列或列組中包含的數據是唯一的。