14.4 UNIQUE 约束

在本教程中，您将了解MySQL UNIQUE约束，以强制一列或一个组合列的值的唯一性。

MySQL UNIQUE约束简介

有时，希望在列中强制执行唯一性值，例如供应商表中供应商的电话必须是唯一的，或者供应商名称和地址的组合不得重复，简单一点理解就是：供应商的名称可以相同，但是不能在同一个地址。

要执行此规则，需要使用UNIQUE约束。

UNIQUE约束是列约束或表约束，它定义了限制列或一组列中的值为唯一的规则。

要将UNIQUE约束添加到列，请使用以下语法：

CREATE TABLE table_1(
    column_name_1  data_type UNIQUE,
);

或者可以将UNIQUE约束定义为表约束，如下所示：

CREATE TABLE table_1(
   ...
   column_name_1 data_type,
   ...
   UNIQUE(column_name_1)
);

如果在column_name_1列中插入或更新导致重复值的值，MySQL将发出错误消息并拒绝更改。

如果要跨列强制执行唯一值，则必须将UNIQUE约束定义为表约束，并将每列用逗号分隔：

CREATE TABLE table_1(

   ...
   column_name_1 data_type,
   column_name_2 data type,
   ...
   UNIQUE(column_name_1,column_name_2)
);

MySQL将使用column_name_1和column_name_2列中的值的组合来评估唯一性。

如果要为UNIQUE约束分配一个指定的名称，可以使用CONSTRAINT子句，如下所示：

CREATE TABLE table_1(
   ...
   column_name_1 data_type,
   column_name_2 data type,
   ...
   CONSTRAINT constraint_name UNIQUE(column_name_1,column_name_2)
);

MySQL UNIQUE约束示例

以下语句创建了一个名为suppliers的新表，具有两个唯一约束条件：

USE testdb;
CREATE TABLE IF NOT EXISTS suppliers (
    supplier_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    phone VARCHAR(16) NOT NULL UNIQUE,
    address VARCHAR(255) NOT NULL,
    CONSTRAINT uc_name_address UNIQUE (name , address)
);

第一个UNIQUE约束应用于phone列。 这意味着每个供应商必须具有不同的电话号码。 换句话说，没有哪两个供应商的电话号码是相同的。 第二个UNIQUE约束的名称为uc_name_address，它强制name和address列中值的唯一性。供应商可以拥有相同的名称或地址，但名称和地址不能同时相同。

我们在suppliers表中插入一些行来测试UNIQUE约束。

以下语句将一行插入到suppliers表中。

INSERT INTO suppliers(name, phone, address)
VALUES('ABC Inc', '13800138000','4000 North 1st Street, San Jose, CA, USA');

尝试插入不同的供应商数据信息，但是使用的电话号码是一个在suppliers表中已经存在电话号码。

INSERT INTO suppliers(name, phone, address)
VALUES('XYZ Corporation', '13800138000','4001 North 1st Street, San Jose, CA, USA');

MySQL发出错误：

Error Code: 1062. Duplicate entry '13800138000' for key 'phone'

我们将电话号码更改为其他号码，然后再次执行插入语句。

INSERT INTO suppliers(name, phone, address)
VALUES('XYZ Corporation', '13800138111','400 North 1st Street, San Jose, CA, USA');

上面插入语句可以成功执行。

现在执行以下INSERT语句来插入一行，其中已经存在的名称和地址列中的值。

INSERT INTO suppliers(name, phone, address)
VALUES('XYZ Corporation', '13800138222','400 North 1st Street, San Jose, CA, USA');

MySQL发出以下错误信息 -

1062 - Duplicate entry 'XYZ Corporation-400 North 1st Street, San Jose, CA, USA' for key 'uc_name_address'

因为违反了UNIQUE约束uc_name_address，所以插入失败。

管理MySQL UNIQUE约束

当您向表中添加唯一约束时，MySQL将为数据库创建一个相应的BTREE索引。 以下SHOW INDEX语句显示在suppliers表上创建的所有索引。

SHOW INDEX FROM testdb.suppliers;

结果如下所示 -

img

如上图所见，有两个BTREE索引对应于创建的两个UNIQUE约束。

要删除UNIQUE约束，可以使用DROP INDEX或ALTER TABLE语句，语法如下所示：

DROP INDEX index_name ON table_name;

或者

ALTER TABLE table_name
DROP INDEX index_name;

例如，要删除suppliers表上的uc_name_address约束，需要以下语句：

DROP INDEX uc_name_address ON suppliers;

再次执行SHOW INDEX语句来验证uc_name_unique约束是否已经被删除。

SHOW INDEX FROM testdb.suppliers;

执行上面查询语句，得到以下结果

img

如果要将UNIQUE约束添加到已存在的表中，该怎么办？很简单，使用ALTER TABLE语句，语法如下所示：

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column_list);

例如，要将名称为uc_name_address的UNIQUE约束添加到suppliers表，请使用以下语句：

ALTER TABLE suppliers
ADD CONSTRAINT uc_name_address UNIQUE (name,address);

请注意，为了使语句成功执行，当然表的name和address列中的值的组合必须是唯一的。

在本教程中，您已经学习了如何使用MySQL UNIQUE约束来强制表中列或一组列中的值的唯一性。