【mysql】唯一性约束unique

文章目录

• 唯一性约束
• • 1. 作用
  • 2. 关键字
  • 3. 特点
  • 4. 添加唯一约束
  • 5. 关于复合唯一约束

唯一性约束

1. 作用

用来限制某个字段/某列的值不能重复。

2. 关键字

UNIQUE

3. 特点

• 同一个表可以有多个唯一约束。
• 唯一约束可以是某一个列的值唯一，也可以多个列组合的值唯一。
• 唯一性约束允许列值为空。
• 在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同。
• MySQL会给唯一约束的列上默认创建一个唯一索引。

4. 添加唯一约束

（1）建表时

• 在CREATE TABLE时添加约束

  CREATE TABLE test2(
  id INT UNIQUE, #列级约束
  last_name VARCHAR(15) ,
  email VARCHAR(25),
  salary DECIMAL(10,2),
  #表级约束
  CONSTRAINT uk_test2_email UNIQUE(email)
  );

• 查看约束

  SELECT * FROM information_schema.table_constraints
  WHERE table_name = ‘test2’;

• 可以看到，没有写 约束名 的时候以定义的列名 id 来命名
  

• 在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同。

  INSERT INTO test2(id,last_name,email,salary)
  VALUES(1,‘Tom’,‘[e-mail]’,4500);

  #错误：Duplicate entry ‘1’ for key ‘test2.id’
  INSERT INTO test2(id,last_name,email,salary)
  VALUES(1,‘Tom1’,‘[e-mail]’,4600);

  #错误：Duplicate entry ‘[e-mail]’ for key ‘test2.uk_test2_email’
  INSERT INTO test2(id,last_name,email,salary)
  VALUES(2,‘Tom1’,‘[e-mail]’,4600);

• 可以向声明为unique的字段上添加null值。而且可以多次添加null

  INSERT INTO test2(id,last_name,email,salary)
  VALUES(2,‘Tom1’,NULL,4600);

INSERT INTO test2(id,last_name,email,salary)
VALUES(3,'Tom3',NULL,4600);

create table 表名称(字段名  数据类型,字段名  数据类型  unique,  字段名  数据类型  unique key,字段名  数据类型
);
create table 表名称(字段名  数据类型,字段名  数据类型,  字段名  数据类型,[constraint 约束名] unique key(字段名)
);

举例：

create table student(sid int,sname varchar(20),tel char(11) unique,cardid char(18) unique key
);CREATE TABLE t_course(cid INT UNIQUE,cname VARCHAR(100) UNIQUE,description VARCHAR(200)
);CREATE TABLE USER(id INT NOT NULL,NAME VARCHAR(25),PASSWORD VARCHAR(16),-- 使用表级约束语法CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD)
);

表示用户名和密码组合不能重复

insert into student values(1,'张三','13710011002','101223199012015623');
insert into student values(2,'李四','13710011003','101223199012015624');mysql> select * from student;
+-----+-------+-------------+--------------------+
| sid | sname | tel         | cardid             |
+-----+-------+-------------+--------------------+
|   1 | 张三  | 13710011002 | 101223199012015623 |
|   2 | 李四  | 13710011003 | 101223199012015624 |
+-----+-------+-------------+--------------------+
2 rows in set (0.00 sec)insert into student values(3,'王五','13710011004','101223199012015624'); #身份证号重复
ERROR 1062 (23000): Duplicate entry '101223199012015624' for key 'cardid'insert into student values(3,'王五','13710011003','101223199012015625'); 
ERROR 1062 (23000): Duplicate entry '13710011003' for key 'tel'

（2）建表后指定唯一键约束

#字段列表中如果是一个字段，表示该列的值唯一。如果是两个或更多个字段，那么复合唯一，即多个字段的组合是唯一的
#方式1：
alter table 表名称 add unique key(字段列表); #方式2：
alter table 表名称 modify 字段名 字段类型 unique;

• 在ALTER TABLE时添加约束

  UPDATE test2
  SET salary = 5000
  WHERE id = 3;

  #方式1：
  ALTER TABLE test2
  ADD CONSTRAINT uk_test2_sal UNIQUE(salary);

#方式2：
ALTER TABLE test2
MODIFY last_name VARCHAR(15) UNIQUE;

#字段列表中如果是一个字段，表示该列的值唯一。如果是两个或更多个字段，那么复合唯一，即多个字段的组合是唯一的
#方式1：
alter table 表名称 add unique key(字段列表); 

5. 关于复合唯一约束

create table 表名称(字段名  数据类型,字段名  数据类型,  字段名  数据类型,unique key(字段列表) #字段列表中写的是多个字段名，多个字段名用逗号分隔，表示那么是复合唯一，即多个字段的组合是唯一的
);#学生表
create table student(sid int,	#学号sname varchar(20),			#姓名tel char(11) unique key,  #电话cardid char(18) unique key #身份证号
);#课程表
create table course(cid int,  #课程编号cname varchar(20)     #课程名称
);#选课表
create table student_course(id int,sid int,cid int,score int,unique key(sid,cid)  #复合唯一
);insert into student values(1,'张三','13710011002','101223199012015623');#成功
insert into student values(2,'李四','13710011003','101223199012015624');#成功
insert into course values(1001,'Java'),(1002,'MySQL');#成功mysql> select * from student;
+-----+-------+-------------+--------------------+
| sid | sname | tel         | cardid             |
+-----+-------+-------------+--------------------+
|   1 | 张三  | 13710011002 | 101223199012015623 |
|   2 | 李四  | 13710011003 | 101223199012015624 |
+-----+-------+-------------+--------------------+
2 rows in set (0.00 sec)mysql> select * from course;
+------+-------+
| cid  | cname |
+------+-------+
| 1001 | Java  |
| 1002 | MySQL |
+------+-------+
2 rows in set (0.00 sec)insert into student_course values
(1, 1, 1001, 89),
(2, 1, 1002, 90),
(3, 2, 1001, 88),
(4, 2, 1002, 56);#成功mysql> select * from student_course;
+----+------+------+-------+
| id | sid  | cid  | score |
+----+------+------+-------+
|  1 |    1 | 1001 |    89 |
|  2 |    1 | 1002 |    90 |
|  3 |    2 | 1001 |    88 |
|  4 |    2 | 1002 |    56 |
+----+------+------+-------+
4 rows in set (0.00 sec)insert into student_course values (5, 1, 1001, 88);#失败#ERROR 1062 (23000): Duplicate entry '1-1001' for key 'sid'   违反sid-cid的复合唯一

• 删除唯一约束

• 添加唯一性约束的列上也会自动创建唯一索引。

• 删除唯一约束只能通过删除唯一索引的方式删除。

• 删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样。

• 如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同；如果是组合列，那么默认和()中排在第一个的列名相同。也可以自定义唯一性约束名。

  SELECT * FROM information_schema.table_constraints
  WHERE table_name = ‘表名’; #查看都有哪些约束

  ALTER TABLE USER
  DROP INDEX uk_name_pwd;

注意：可以通过 show index from 表名称; 查看表的索引