mysql column unique_MySQL UNIQUE 约束

MySQL 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 的表，其中包含两个UNIQUE约束：

CREATE TABLE IF NOT EXISTS suppliers (

supplier_id INT PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(255) NOT NULL,

phone VARCHAR(12) NOT NULL UNIQUE,

address VARCHAR(255) NOT NULL,

CONSTRAINT uc_name_address UNIQUE (name , address)

);

第一个UNIQUE约束应用于phone列。这意味着每个供应商都必须拥有不同的电话号码。换句话说，没有两个供应商拥有相同的电话号码。

第二个UNIQUE约束具有一个名称uc_name_address，名称强制使用名称和地址列中值的唯一性。这意味着供应商可以使用相同的名称或地址，但不能使用相同的名称和地址。

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

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

INSERT INTO suppliers(name, phone, address)

VALUES('ABC Inc', '408-908-2476','4000 North 1st Street, San Jose, CA, USA');

Query OK, 1 row affected (0.01 sec)

我们尝试插入不同的供应商，但具有suppliers表中已存在的电话号码。

INSERT INTO suppliers(name, phone, address)

VALUES('XYZ Corporation', '408-908-2476','4001 North 1st Street, San Jose, CA, USA');

MySQL发出错误：

ERROR 1062 (23000): Duplicate entry '408-908-2476' for key 'phone'

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

INSERT INTO suppliers(name, phone, address)

VALUES('XYZ Corporation', '408-908-2567','400 North 1st Street, San Jose, CA, USA');

Query OK, 1 row affected (0.00 sec)

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

INSERT INTO suppliers(name, phone, address)

VALUES('XYZ Corporation', '408-908-102','400 North 1st Street, San Jose, CA, USA');

MySQL发出错误。

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

因为uc_name_address违反了UNIQUE约束。

管理MySQL UNIQUE约束

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

SHOW INDEX FROM mysqldemo.suppliers;

+-----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+-----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| suppliers | 0 | PRIMARY | 1 | supplier_id | A | 2 | NULL | NULL | | BTREE | | |

| suppliers | 0 | phone | 1 | phone | A | 2 | NULL | NULL | | BTREE | | |

| suppliers | 0 | uc_name_address | 1 | name | A | 2 | NULL | NULL | | BTREE | | |

| suppliers | 0 | uc_name_address | 2 | address | A | 2 | NULL | NULL | | BTREE | | |

+-----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

4 rows in set (0.00 sec)

如您所见，有两个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 mysqldemo.suppliers;

+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| suppliers | 0 | PRIMARY | 1 | supplier_id | A | 2 | NULL | NULL | | BTREE | | |

| suppliers | 0 | phone | 1 | phone | A | 2 | NULL | NULL | | BTREE | | |

+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

2 rows in set (0.00 sec)

如果要将UNIQUE约束添加到已存在的表中，怎么办？

为此，请使用以下ALTER TABLE语句：

ALTER TABLE table_name

ADD CONSTRAINT constraint_name UNIQUE (column_list);

例如，要将uc_name_addressUNIQUE约束添加回suppliers表，请使用以下语句：

ALTER TABLE suppliers

ADD CONSTRAINT uc_name_address UNIQUE (name,address);

+-----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+-----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| suppliers | 0 | PRIMARY | 1 | supplier_id | A | 2 | NULL | NULL | | BTREE | | |

| suppliers | 0 | phone | 1 | phone | A | 2 | NULL | NULL | | BTREE | | |

| suppliers | 0 | uc_name_address | 1 | name | A | 2 | NULL | NULL | | BTREE | | |

| suppliers | 0 | uc_name_address | 2 | address | A | 2 | NULL | NULL | | BTREE | | |

+-----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

4 rows in set (0.00 sec)

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

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