06【数据库的约束】

---

上一篇：05【数据的备份与恢复】

下一篇：07【连接查询】

目录：【MySQL零基础系列教程】

---

---

06【数据库的约束】

6.1 数据库约束的概述

6.1.1 约束的作用：

一般在创建表的时候给表的字段添加各种约束，从而保证输入到表中的数据是正确的。保证数据的正确性，完整性和有效性。违反约束的数据是不能添加到表中去的。如果表已经存在，并且表中已经有数据，添加约束的时候如果表中的数据已经违反了现在要添加的约束，约束会添加失败。

6.1.2 约束种类：

6.2 主键约束

6.2.1 主键的作用

用来唯一标识表中的每一行记录，在创建表的时候，每张表都应该创建一个主键，每个表只能有一个主键约束，只要有主键就有主键约束。

6.2.2 主键的特点

• 1）非空：必须要有值
• 2）唯一：同一张表中不能出现重复数据
• 3）一张表最多只能有一个主键

6.2.3 主键列的选择

通常不建议使用与业务相关的字段做为主键，如：身份证、手机号等。往往会单独创建一个字段来做为主键，主键应该是没有含义。主键是给程序员编程使用的，不是给最终用户使用。

6.2.4 创建主键方式

• 1）在创建表的时候添加主键：

字段名 字段类型 PRIMARY KEY

create table t1(
	id int primary key,				-- 指定id列为主键
    city varchar(20)
);

insert into t1 values(1,'四川成都');

-- ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' 违反主键约束
insert into t1 values(1,'浙江杭州');
1
2
3
4
5
6
7
8
9
10
11

• 2）在已有表中添加主键

ALTER TABLE 表名 ADD PRIMARY KEY(字段名);

alter table t1 add primary key(id);
1
2
3

6.2.5 删除主键

-- 语法:
alter table 表名 drop primary key;

-- 删除t1表的主键约束
alter table t1 drop primary key;

-- 查看表详情
desc t1;

-- 给t1表的id列添加一个主键约束
alter table t1 add primary key (id);

-- 查看表详情
desc t1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

6.2.6 主键自增

主键如果让我们自己添加很有可能重复，我们通常希望在每次插入新记录时，数据库自动生成主键字段的值，在最大值加1做为主键值。自增长必须是整数类型，而且必须是主键可以使用。

• 主键自增的语法：

字段名 字段类型 PRIMARY KEY AUTO_INCREMENT
1

create table t2(
	id int primary key auto_increment,		-- 主键并且自增
    city varchar(20)
);

-- 查看表详情
desc t2;

-- 插入数据
-- 错误
insert into t2 values('黑龙江哈尔滨');

-- 执行几次
insert into t2 (city) values('云南昆明');
insert into t2 (city) values('甘肃兰州');
insert into t2 (city) values('青海西宁');
insert into t2 (city) values('山西太原');
insert into t2 (city) values('辽宁沈阳');
insert into t2 (city) values('内蒙古呼和浩特');

select * from t2;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

• 修改主键自增的起始值

-- 将主键的起始值设置为100

alter table t2 auto_increment = 100;


insert into t2(city) values('山东济南');

-- 第二种写法
insert into t2 values(NULL,'吉林长春');

select * from t2;
1
2
3
4
5
6
7
8
9
10
11

6.2.7 联合主键

主键可以不只一个字段，如果有多个字段组成的主键，称为联合主键。

• 示例：

create table test(
	id int,
	id2 int,
	city varchar(20),
	primary key(id,id2)			-- id和id2列组合为联合主键
);

insert into test values(1,1,'北京');
insert into test values(1,2,'天津');
insert into test values(1,1,'重庆');			-- 联合主键冲突
1
2
3
4
5
6
7
8
9
10

6.3 唯一约束

概念：这一列的值不能重复

• 语法：

字段名 字段类型 UNIQUE
1

• 添加实现唯一约束

-- 创建学生表st3, 包含字段(id, name),name这一列设置唯一约束,不能出现同名的学生

create table t3 (
	id int primary key auto_increment,
	city varchar(20) unique
);

-- 添加一个同名的学生
insert into t3 (city) values('河北石家庄');

-- ERROR 1062 (23000): Duplicate entry '河北石家庄' for key 'city'
insert into t3 (city) values('河北石家庄');

desc t3;

select * from t3;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

6.4 非空约束

概念：这一列的值必须输入，不能为空

• 语法：

字段名 字段类型 NOT NULL

create table t4(
	id int,
	province varchar(30),
	city varchar(20) not null			-- 非空约束
);

insert into t4 values(1,'江苏','南京');

-- ERROR 1048 (23000): Column 'city' cannot be null		city列不能为空
insert into t4 values(2,'江苏',null);

-- ERROR 1364 (HY000): Field 'city' doesn't have a default value	city列没有默认值
insert into t4(id,province) values(2,'江苏');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

6.5 默认值

概念：如果某一列没有输入值，使用默认值。如果输入了，则使用输入的值。

• 语法：

字段名 字段类型 default 默认值

create table t5(
	id int,
	province varchar(20),
	city varchar(20) default '乌鲁木齐'			-- 如果该列没填,默认为乌鲁木齐
);

-- 如果填了以真实的值为准
insert into t5 values(1,'西藏','拉萨');

insert into t5 values(2,'新疆');			-- 报错,不能直接不填city列


-- 可以使用MySQL提供的default关键字
insert into t5 values(3,'新疆',default);

-- 没有填默认为'乌鲁木齐'
insert into t5(id,province) values(2,'新疆');

-- 查询表数据
select * from t5;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

6.6 检查约束

检查约束可以使用一定的范围条件来约束我们的列的值，例如年龄应该在0~120岁之间，性别只能有男或女等；

• 语法：

create table t6(
	id int,
	name varchar(30),
	age int check(age>0 and age<120),			-- 年龄只在0~120之间
	sex char(1) check('男' or '女') 			-- 性别只能在'男' 和 '女'之间选择
);

-- 数据合格
insert into t6 values(null,'小明',20,'男');


-- 违反年龄的检查约束
insert into t6 values(null,'小黄',0,'男');

-- 违反年龄的检查约束
insert into t6 values(null,'小陈',150,'男');

-- 违反性别的检查约束
insert into t6 values(null,'小王',20,'啊');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

tips：在MySQL5.7版本不支持检查约束，我们了解即可；

MySQL官方文档：https://dev.mysql.com/doc/refman/5.7/en/create-table.html#create-table-indexes-keys

6.7 外键约束

6.7.1 单表的缺点

创建一个员工表包含如下列(id, name, age, dep_name,dep_location),id主键并自动增长,添加5条数据

CREATE TABLE employee (  
	id INT PRIMARY KEY AUTO_INCREMENT,  
	name VARCHAR(30),  
	age INT,  
	dept_name VARCHAR(30),  
	dept_location VARCHAR(30)  
);  

-- 添加数据  
INSERT INTO employee (name, age, dept_name, dept_location) VALUES 
('小明', 25,'研发部', '贵州贵阳');  
INSERT INTO employee (name, age, dept_name, dept_location) VALUES 
('小龙', 24,'研发部', '贵州贵阳');  
INSERT INTO employee (name, age, dept_name, dept_location) VALUES 
('小红', 20,'研发部', '贵州贵阳');  

INSERT INTO employee (name, age, dept_name, dept_location) VALUES 
('小兰', 26,'销售部', '宁夏银川');  
INSERT INTO employee (name, age, dept_name, dept_location) VALUES 
('小陈', 28,'销售部', '宁夏银川');  
INSERT INTO employee (name, age, dept_name, dept_location) VALUES 
('小赵', 18,'销售部', '宁夏银川');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

• 以上数据表的缺点:

  1. 大量冗余数据出现：研发部、销售部、地址等信息出现了多次

  2. 会出现删除异常，如果研发部一个人都没有那么研发部就不存在了

• 解决方案：

把这一张表拆分成两张表，一张表保存员工，另一张表保存部门。两个表之间通过一个外键建立联系。

-- 主表: 部门表
create table dept(
	id int primary key auto_increment,
	dept_name varchar(20),
	dept_location varchar(20)
);

-- 添加部门信息
insert into dept (dept_name,dept_location) values ('研发部','贵州贵阳'),('销售部', '宁夏银川');

-- 如果存在就把这个表删除
drop table if exists employee;

-- 员工表
create table employee(
	id int primary key auto_increment,
	name varchar(20),
	age int,
	dept_id int 	-- 外键的数据类型与主表中的主键相同
);

-- 添加员工信息
INSERT INTO employee (name, age,dept_id) VALUES 
('小明', 25,1),
('小龙', 24,1),
('小红', 20,1),
('小兰', 26,2),
('小陈', 28,2),
('小赵', 18,2);  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

• 问题：当我们在employee的dept_id里面输入不存在的部门，数据依然可以添加，但是并没有对应的部门，实际应用中不能出现这种情况。employee的dept_id中的数据只能是dept表中存在的id

-- 插入3号部门的员工已经可以正常执行
INSERT INTO employee (name, age,dept_id) VALUES ('小黄',23,3);
1
2

6.7.2 什么是外键约束

部门与员工之间是一对多的关系，一个部门对应多个员工，一个员工属于一个部门。部门是1方，员工是多方。

• 主表： 是一方，部门表

• 从表： 是多方，员工表

• 什么是外键：外键出现在从表中，被主表的主键约束的那一列外键

6.7.3 创建外键

• 新建表时增加外键-语法：

[CONSTRAINT `外键名`] FOREIGN KEY(外键字段) REFERENCES 主表(主键)
1

• 示例代码：

-- 删除员工表(从表)
drop table employee;

-- 创建员工表(id,name,age,dep_id)

create table employee(
	id int primary key auto_increment,
	name varchar(20),
	age int,
	dept_id int, -- 外键的数据类型与主表中的主键相同
	CONSTRAINT `employee_ibfk_1` foreign key (dept_id) references dept(id)		-- 本表的dept_id列依赖于dept表的id列
);

-- 添加员工信息
INSERT INTO employee (name, age,dept_id) VALUES 
('小明', 25,1),
('小龙', 24,1),
('小红', 20,1),
('小兰', 26,2),
('小陈', 28,2),
('小赵', 18,2);  

-- 违反外键约束
INSERT INTO employee (name, age,dept_id) VALUES ('小黄',23,3);
/*
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db01`.`employee`, CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`))
*/

-- 添加3号部门
insert into dept values(3,'行政部','陕西西安');

-- 再次添加员工并指定为三号部门
INSERT INTO employee (name, age,dept_id) VALUES ('小黄',23,3);

select * from employee;

select * from dept;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

• 已有表增加外键-语法：

# 新增:
ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY (外键字段) REFERENCES 主表(主键)
1
2

• 示例代码：

-- 添加约束
alter table employee add constraint employee_ibfk_1 foreign key(dept_id) references dept(id);

-- 查看表的建表语句
show create table employee;
1
2
3
4
5

6.7.4 删除外键

• 语法：

ALTER TABLE 表名 DROP FOREIGN KEY 约束名;

alter table employee drop foreign key employee_ibfk_1;

-- 查看表的建表语句
show create table employee;
1
2
3
4
5
6

6.7.5 外键的级联

• 测试数据：

-- 主表: 部门表
create table dept(
	id int primary key auto_increment,
	dept_name varchar(20),
	dept_location varchar(20)
);

-- 添加部门信息
insert into dept (dept_name,dept_location) values ('研发部','贵州贵阳'),('销售部', '宁夏银川');

-- 如果存在就把这个表删除
drop table if exists employee;

-- 员工表
create table employee(
	id int primary key auto_increment,
	name varchar(20),
	age int,
	dept_id int, 	-- 外键的数据类型与主表中的主键相同
	CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`)
);

-- 添加员工信息
INSERT INTO employee (name, age,dept_id) VALUES 
('小明', 25,1),
('小龙', 24,1),
('小红', 20,1),
('小兰', 26,2),
('小陈', 28,2),
('小赵', 18,2);  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

1）级联问题引入

• 出现新的问题：要把部门表中的id值2，改成20，能不能直接更新呢？

update dept set id=20 where id=2; 	-- ERROR 1451 (23000): Cannot delete or update a parent row
1

当把部门的id修改为20后，原来为2号部门的员工怎么办？

• 要删除部门id等于1的部门, 能不能直接删除呢？

delete from dept where id=1;  		-- ERROR 1451 (23000): Cannot delete or update a parent row
1

如果把id为1的部门删除后，原来在1号部门的员工怎么办？

当建立外键约束后，在修改和删除主表的主键时，从表必须设置一定的级联操作；如：随着主表修改而修改，或随着主表的删除而删除；默认情况下，建立外键约束后，MySQL不允许删除或修改主表的主键列；

2）级联的种类

• 删除级联：
  • on delete set null：如果主表有删除，那么从表的数据都为null
  • on delete cascade：如果主表有删除，那么从表的数据也删除
  • on delete restrict：如果设置该值，主表不允许做删除操作（默认的外键行为）
  • on delete no action：即如果存在从数据，不允许删除主数据（和restrict类似）。
• 修改级联：
  • on update set null：如果主表有更新，那么从表的数据都为null
  • on update cascade：如果主表有更新，那么从表的数据也更新
  • on update restrict：如果设置该值，主表不允许做更新操作（默认的外键行为）
  • on update no action：如果从表存在对应数据，不允许更新主表数据（和restrict类似）。

3）级联操作

-- 删除外键约束-- 删除外键约束
alter table employee drop FOREIGN KEY employee_ibfk_1;

-- 添加外键约束，级联更新和级联删除
alter table employee add constraint employee_ibfk_1 foreign key (dept_id) references dept(id) on update cascade on delete cascade;

select * from employee;

select * from dept;
1
2
3
4
5
6
7
8
9

• 测试修改级联：

mysql> update dept set id=10 where id=1;		# 把部门表中id等于1的部门改成id等于10
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from employee;
+----+------+------+---------+
| id | name | age  | dept_id |
+----+------+------+---------+
|  1 | 小明 |   25 |      10 |
|  2 | 小龙 |   24 |      10 |
|  3 | 小红 |   20 |      10 |
|  4 | 小兰 |   26 |       2 |
|  5 | 小陈 |   28 |       2 |
|  6 | 小赵 |   18 |       2 |
+----+------+------+---------+
6 rows in set (0.00 sec)

mysql>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

• 测试删除级联：

mysql> delete from dept where id=2;				-- 删除部门号是2的部门
Query OK, 1 row affected (0.00 sec)

mysql> select * from employee;				
+----+------+------+---------+
| id | name | age  | dept_id |
+----+------+------+---------+
|  1 | 小明 |   25 |      10 |
|  2 | 小龙 |   24 |      10 |
|  3 | 小红 |   20 |      10 |
+----+------+------+---------+
3 rows in set (0.00 sec)

mysql>
1
2
3
4
5
6
7
8
9
10
11
12
13
14

6.8 数据约束小结