MySQL 唯一约束
1 特性
- 该约束修饰的字段不能重复
- 每张表可以存在多个唯一约束
- 唯一约束的字段可以为空
2 添加
2.1 建表的时候添加唯一约束
1、UNIQUE直接跟在字段后面指定
CREATE TABLE user1 (
id INT,
name VARCHAR(20) UNIQUE
);
2、UNIQUE在最后指定(单个)
CREATE TABLE user2 (
id INT,
name VARCHAR(20),
UNIQUE(name)
);
3、UNIQUE在最后指定(多个)
CREATE TABLE user3 (
id INT,
name VARCHAR(20),
UNIQUE(id, name)
);
2.2 利用ALTER ADD 添加唯一约束
1、先创建一个没有唯一约束的表
CREATE TABLE user4 (
id INT,
name VARCHAR(20)
);
2、利用 ALTER ADD 语句添加单个唯一约束
ALTER TABLE user4 ADD UNIQUE(name);
添加完响应字段的Key列的值变为”UNI“
mysql> desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
3、利用 ALTER ADD 语句添加联合唯一约束
ALTER TABLE user4 ADD UNIQUE(id, name);
添加完括号里的第一个字段(这里是Id)的Key列的值变为”MUL“,如果要删除的话,只需要删除Key为”MUL“的字段(即id)即可
mysql> DESC USER4;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | MUL | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
2.3 利用ALTER MODIFY字段添加唯一约束
ALTER TABLE user4 MODIFY id INT UNIQUE;
2.4 利用CREATE UNIQUE INDEX创建唯一约束
CREATE UNIQUE INDEX UserIDIndex ON user4 (id);
3 UNIQUE INDEX 的作用
3.1 唯一约束
添加了唯一约束的字段不允许重复。例如在添加了id为1的数据后,再次添加id为1的数据就会报错
# 添加唯一约束
mysql> ALTER TABLE user4 ADD UNIQUE INDEX(ID);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 先插入一条id为1,name为Jason的数据
mysql> INSERT INTO user4 (id, name) VALUES (1, 'Jason');
Query OK, 1 row affected (0.00 sec)
#再次插入一条id为1,name为Jason的数据时就会报重复输入的错误
mysql> INSERT INTO user4 (id, name) VALUES (1, 'Jason');
ERROR 1062 (23000): Duplicate entry '1' for key 'user4.id'
3.2 联合唯一约束
当多个字段在一起添加了联合唯一约束后,只要联合后的字段不重复即可
#添加一个id name的联合主键
mysql> ALTER TABLE user4 ADD UNIQUE INDEX (id, name);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
#插入id为1,name为Jason的数据
mysql> INSERT INTO user4 (id,name) VALUES (1,'Jason');
Query OK, 1 row affected (0.00 sec)
#再次插入id为1,name为Jason的数据,与3.1中仅以id为唯一约束的错误提示
#('1' for key 'user4.id')不同的是,这里的错误提示是('1-Jason' for key 'user4.id'),
# 可以看到是将id-name作为一个整体来进行唯一性校验的
mysql> INSERT INTO user4 (id,name) VALUES (1,'Jason');
ERROR 1062 (23000): Duplicate entry '1-Jason' for key 'user4.id'
#插入id为1,name为Sam的数据(id=1重复)
mysql> INSERT INTO user4 (id,name) VALUES (1,'Sam');
Query OK, 1 row affected (0.00 sec)
#插入id为2,name为Sam的数据(name=Sam重复)
INSERT INTO user4 (id,name) VALUES (2,'Sam');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM user4;
+------+-------+
| id | name |
+------+-------+
| 1 | Jason |
| 1 | Sam |
| 2 | Sam |
+------+-------+
3 rows in set (0.00 sec)
4 删除
4.1 删除匿名唯一索引-删除约束字段
如以下方式创建的匿名唯一约束,直接用ALTER DROP 的方式删除唯一索引,若是联合唯一索引,则删除表中Key列的值为”MUL“的字段名即可。
#单独的唯一索引
mysql> ALTER TABLE user4 ADD UNIQUE INDEX(name);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> ALTER TABLE user4 DROP INDEX name;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
#联合唯一索引
mysql> ALTER TABLE user4 ADD UNIQUE INDEX (id, name);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC User4;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | MUL | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> ALTER TABLE user4 DROP INDEX id;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
4.2 删除具名唯一索引-删除索引名称
#单个
mysql> ALTER TABLE user4 ADD UNIQUE INDEX `NameIndex` (name);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> ALTER TABLE user4 DROP INDEX `NameIndex`;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
#联合
mysql> ALTER TABLE user4 ADD UNIQUE INDEX `IdNameIndex` (id,name);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | MUL | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> ALTER TABLE user4 DROP INDEX `IdNameIndex`;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0