MySQL讲义第11讲——完整性约束之唯一（UNIQUE）约束

定义了 UNIQUE 约束的字段不能包含重复值，可以为一个或多个字段定义 UNIQUE 约束。因此，UNIQUE 即可以在字段级也可以在表级定义，在 UNIQUE 约束的字段上可以包含空值。

当需要限定某个表字段的取值都唯一、没有重复值时使用 UNIQUE 约束，例如：注册邮箱时的邮箱名、手机号等信息。

一、UNIQUE 约束与 PRIMARY KEY 约束的区别和联系

（1）唯一性约束所在的列允许空值，但是主键约束所在的列不允许空值。
（2）可以把唯一性约束放在一个或者多个列上，这些列或列的组合必须有唯一的。但是，唯一性约束所在的列并不是表的主键列。
（3）唯一性约束强制在指定的列上创建一个唯一性索引。
（4）一个表最多只有一个主键，但可以有多个唯一键。
（5）UNIQUE 约束 = PRIMARY KEY 约束 + NOT NULL 。

二、创建表时，同时创建 UNIQUE 约束

1、定义列的同时定义 UNIQUE 约束

语法如下：

create table 表名 (
    列名 类型 ... ,
    列名 类型 unique,
    ....
);
说明：该方法不能指定约束名。可以使用【show index from 表名; 】命令查看索引名。

举例：

mysql> create table t12 (
    ->     id int primary key auto_increment,
    ->     name char(20),
    ->     phone char(11) unique,
    ->     ID_number char(18) unique
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc t12;
+-----------+----------+------+-----+---------+----------------+
| Field     | Type     | Null | Key | Default | Extra          |
+-----------+----------+------+-----+---------+----------------+
| id        | int(11)  | NO   | PRI | NULL    | auto_increment |
| name      | char(20) | YES  |     | NULL    |                |
| phone     | char(11) | YES  | UNI | NULL    |                |
| ID_number | char(18) | YES  | UNI | NULL    |                |
+-----------+----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> show index from t12;
+-------+------------+-----------+--------------+-------------+-----------+-------------
| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality 
+-------+------------+-----------+--------------+-------------+-----------+-------------
| t12   |          0 | PRIMARY   |            1 | id          | A         |           0 
| t12   |          0 | phone     |            1 | phone       | A         |           0 
| t12   |          0 | ID_number |            1 | ID_number   | A         |           0 
+-------+------------+-----------+--------------+-------------+-----------+-------------
3 rows in set (0.00 sec)

-- unique 约束的名称默认和字段名相同。

2、在所有的列定义之后定义 UNIQUE 约束

语法如下：

create table 表名 (
    列名 类型 ... ,
    [constraint 约束名] unique(列名)
);
说明：该方法可以指定约束名。可以使用【show index from 表名; 】命令查看索引名。

举例：

mysql> create table t13 (
    ->  id int primary key auto_increment,
    ->  name char(20),
    ->  phone char(11),
    ->  ID_number char(18),
    ->  constraint uq_phone unique(phone),
    ->  constraint uq_ID_number unique(ID_number)
    ->);
Query OK, 0 rows affected (0.04 sec)

mysql> desc t13;
+-----------+----------+------+-----+---------+----------------+
| Field     | Type     | Null | Key | Default | Extra          |
+-----------+----------+------+-----+---------+----------------+
| id        | int(11)  | NO   | PRI | NULL    | auto_increment |
| name      | char(20) | YES  |     | NULL    |                |
| phone     | char(11) | YES  | UNI | NULL    |                |
| ID_number | char(18) | YES  | UNI | NULL    |                |
+-----------+----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> show index from t13;
+-------+------------+--------------+--------------+-------------+-----------
| Table | Non_unique | Key_name     | Seq_in_index | Column_name | Collation 
+-------+------------+--------------+--------------+-------------+-----------
| t13   |          0 | PRIMARY      |            1 | id          | A         
| t13   |          0 | uq_phone     |            1 | phone       | A         
| t13   |          0 | uq_ID_number |            1 | ID_number   | A         
+-------+------------+--------------+--------------+-------------+-----------
3 rows in set (0.00 sec)

三、创建表之后添加 UNIQUE 约束

创建表之后可以添加 UNIQUE 约束，语法格式如下：

alter table 表名 add [constraint 约束名] unique(列名);

举例：

mysql> create table t14(
    ->     id int primary key auto_increment,
    ->     name char(20),
    ->     phone char(11)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> alter table t14 add constraint uq_phone unique(phone);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t14;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(11)  | NO   | PRI | NULL    | auto_increment |
| name  | char(20) | YES  |     | NULL    |                |
| phone | char(11) | YES  | UNI | NULL    |                |
+-------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> show index from t14;
+-------+------------+----------+--------------+-------------+-----------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation 
+-------+------------+----------+--------------+-------------+-----------
| t14   |          0 | PRIMARY  |            1 | id          | A         
| t14   |          0 | uq_phone |            1 | phone       | A         
+-------+------------+----------+--------------+-------------+-----------
2 rows in set (0.00 sec)

四、删除 UNIQUE 约束

删除 UNIQUE 约束之前可以使用 【show index from 表名; 】查看 UNIQUE 约束的名称。删除 UNIQUE 约束的命令格式如下：

alter table 表名 drop index 约束名;

举例：

mysql> show index from t13;
+-------+------------+--------------+--------------+-------------+-----------
| Table | Non_unique | Key_name     | Seq_in_index | Column_name | Collation 
+-------+------------+--------------+--------------+-------------+-----------
| t13   |          0 | PRIMARY      |            1 | id          | A         
| t13   |          0 | uq_phone     |            1 | phone       | A         
| t13   |          0 | uq_ID_number |            1 | ID_number   | A         
+-------+------------+--------------+--------------+-------------+-----------
3 rows in set (0.00 sec)

mysql> alter table t13 drop index uq_ID_number;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from t13;
+-------+------------+----------+--------------+-------------+-----------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation 
+-------+------------+----------+--------------+-------------+-----------
| t13   |          0 | PRIMARY  |            1 | id          | A         
| t13   |          0 | uq_phone |            1 | phone       | A         
+-------+------------+----------+--------------+-------------+-----------
2 rows in set (0.00 sec)