【⑫MySQL | 约束(二)】外键 | 默认值 | 检查约束 — 综合案例

前言

✨欢迎来到小K的mysql专栏，本节将为大家带来Mysql外键 | 默认值 | 检查约束 以及综合案例的分享✨

6. 外键约束(FOREIGN KEY,FK)

前面介绍的完整性约束都是在单表中进行设置，而外键约束则保证多个表(通常为两个表）之间的参照完整性，即构建于两个表的两个字段之间的参照关系。

6.1 概念

设置外键约束的两个表之间会具有父子关系，即子表中某个字段的取值范围由父表所决定。例如，表示一种部门和雇员关系，即每个部门有多个雇员。首先应该有两个表：部门表和雇员表，然后雇员表中有一个表示部门编号的字段deptno，其依赖于部门表的主键，这样字段deptno就是雇员表的外键，通过该字段部门表和雇员表建立了关系。

对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）。

在具体设置FK约束时，设置FK约束的字段必须依赖于数据库中已经存在的父表的主键，同时外键可以为NULL。

6.2 特点

• 从表的外键列必须引用(参考)主表的主键或唯一约束的列：因为参考的值必须是唯一的
• 在创建外键约束时，如果不给外键约束命名，默认名不是列名，而是自动产生一个外键名，如emp11_ibfk_1，也可以指定外键约束名
• 创建表时必须先创建主表，再创建从表
• 删除表是必须先删除从表(或先删除外键约束)，再删除主表
• 当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该字段的记录，然后才可以删除主表中的记录
• 当创建外键约束时，系统默认会在所在的列上建立对应的普通索引；删除外键约束之后，必须手动删除对应的索引

6.3 添加外键约束

设置表中某字段的FK 约束非常简单，其语法形式如下:

CREATE TABLE table_name(
	字段名 数据类型，
    字段名 数据类型，
    ...
    CONSTRAINT 外键约束名 FOREIGN KEY (字段名1)
    	REFERENCES 主表名 (字段名2)
);

在上述语句中，“外键约束名”用来标识约束名，“字段名1”是子表中设置外键的字段名，“字段2”是子表参照的父表中字段名。

（1）建表时添加

CREATE TABLE 主表名
(
	字段名 数据类型 PRIMARY KEY,
    字段名 数据类型，
    ...
);

CREATE TABLE 从表名
(
    字段名 数据类型 PRIMARY KEY,
    字段名 数据类型，
    ...
    CONSTRAINT 约束名 FOREIGN KEY(外键约束字段名)
    REFERENCES 主表(参考字段名)
);

（1）建表后添加

一般情况下，表与表的关联都是提前设计好的，因此，会在创建表的时候就把外键约束定义好。不过，如果需要修改表的设计（比如添加新的字段，增加新的关联关系），但是没有预先定义外键约束，那么，就要用修改表的方式来补充定义。

格式：

ALTER TABLE 从表名 ADD [CONSTRAINT 约束名] FOREIGN KEY(从表字段名) REFERENCES 主表名(被参考字段) [ON UPDATE XX][ON DELETE XX];

范例：

ALTER TABLE emp ADD [CONSTRAINT fk_emp_deptno] FOREIGN KEY(deptno) REFERENCES dept(deptno);

6.4 演示

6.4.1 创建表

#新建数据库
CREATE DATABASE db_maye;
USE db_maye;

#创建表
CREATE TABLE dept
(
	deptno INT PRIMARY KEY ***MENT '部门编号',
	dname VARCHAR(20) NOT NULL  ***MENT '部门名称',
	loc   VARCHAR(20)  ***MENT '部门所在位置'
);

CREATE TABLE emp
(
	empno INT PRIMARY KEY  ***MENT '员工编号',
	ename VARCHAR(10) NOT NULL  ***MENT '员工姓名',
	deptno INT  ***MENT '员工所在部门编号',	#外键必须使用表级约束
	CONSTRAINT fk_emp_deptno FOREIGN key(deptno) REFERENCES dept(deptno)
);
#必须先创建dept表，再创建emp表，如果没有指定外键，那么随便先创建哪个都行

6.4.2操作表

• 添加记录

#给员工表添加一条记录
INSERT INTO emp(empno,ename,deptno) VALUES(1234,'king',10);
-- 在dept表中不存在10号部门，所以错误->error:无法添加或更新子行:外键约束失败(' db_maye ')。 外键约束:fk_emp_deptno (' deptno ')

#先给dept添加记录，再执行上面的插入语句即可
INSERT INTO dept(deptno,dname,loc) VALUES(10,'C/C++','武汉');

• 删除记录

#删除主表dept中的记录
DELETE FROM dept WHERE deptno=10;
-- 在从表emp中有部门编号为10的，所以错误->Cannot delete or update a parent row: a foreign key constraint fails...

#先删除从表emp中参考指定值的记录,再执行上面的删除语句即可
DELETE FROM emp WHERE deptno=10;

• 修改记录

#修改主表dept中的部门编号(把部门编号为10的改为20)
UPDATE dept SET deptno=20 WHERE deptno=10;
-- 在emp表中有参考dept表的deptno的外键，所以不能修改-> Cannot delete or update a parent row: a foreign key constraint fails...

#可以先删除从表emp中参考指定值的记录，再执行上面的语句
DELETE FROM emp WHERE deptno=10;

• 创建完表后添加外键约束

ALTER TABLE emp ADD [CONSTRAINT fk_emp_deptno] FOREIGN KEY(deptno) REFERENCES dept(deptno);

• 删除约束和索引

#先查看约束名
SELECT * FROM information_schema.TABLE_CONSTRAINTS
WHERE table_name='emp';

#删除外键约束
ALTER TABLE emp DROP FOREIGN KEY fk_emp_deptno;

#查看指定表的索引
SHOW INDEX FROM emp;
#最后手动删除索引
ALTER TABLE emp DROP INDEX fk_emp_deptno;

6.5 开发场景

Ques 1：如果两个表之间有关系(一对一，一对多)，比如员工表和部门表（一对多），他们之间是否一定要建立外键约束？

Answer：不是的，没有外键约束，照样用的好好的！！

Ques 2：建不建外键约束有什么区别？

Answer：

• 建立外键约束，你的操作（创建表、删除表，添加、修改、删除数据）会受到限制。

  • 例如：在员工表中不可能添加一个员工信息，如果他的部门的编号在部门表找不到。

• 不建立外键约束，你的操作（创建表、删除表，添加、修改、删除数据）不会受到限制，但也要保证数据的引用完整性，只能依靠程序员的自觉或者在访问的时候进行限定。

  • 列如在员工表中，可以添加一个员工信息，即使他的部门不在部门表中

Ques 3：那么建不建外键约束和查询有没有关系？

Answer：没有

在MySQL里，外键约束是有成本的，需要消耗系统资源。对于大并发的SQL操作，有可能会不适合。比如大型网站的中央数据库，可能会因为外键约束的系统开销而变得非常慢。所以，MySQL允许你不使用系统自带的外键约束，在应用层面完成检查数据一致性的逻辑。也就是说，即使你不用外键约束，也要想办法通过应用层免得附加逻辑来实现外键约束的功能，确保数据的一致性。

6.6 阿里开发规范

【强制】不得使用外键与级联，一切外键概念必须在应用层解决。

说明：部门表dept中的deptno是主键，那么员工表emp中的empno则为外键。如果更新部门表中的deptno，同时触发员工表中的deptno更新，即为级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。

6.7 级联操作

当我们需要删除部门表(主表)信息时，必须先删除员工表(从表)中关联的数据，很麻烦！！！

这时候我们就可以用到级联操作：级联操作指的就是，当你操作主表时，自动的操作从表

两种级联操作

• 级联删除：当删除主表数据是时自动删除从表中相关数据
• 级联更新：当主表外键约束字段(一般是主键)更新时，自动更新从表的数据

五种级联方式

定义从表的外键时指定的ON UPDATE/ON DELETE子句, InnoDB支持５种方式，分列如下 ：

• CASCADE 级联方式

  • 在主表上update/delete记录时，同步update/delete掉从表的匹配记录

• SET NULL 设置为NULL

  • 在主表上update/delete记录时，将从表上匹配记录的列为NULL

• NO ACTION 不允许更新和删除

  • 如果从表中有匹配的记录，则不允许对主表的关联字段更新

• RESTRICT 限制

  • 同NO ACTION

• SET DEFAULT

  • 主表有变更时，子表将外键列设置成一个默认的值，但Innodb不能识别…

范例：可以看到下图中从表的数据随着主表数据的修改变化了且主表被限制不能删除数据

CREATE TABLE emp1
(
	empno INT PRIMARY KEY  ***MENT '员工编号',
	ename VARCHAR(10) NOT NULL  ***MENT '员工姓名',
	deptno INT  ***MENT '员工所在部门编号',	#外键必须使用表级约束
	CONSTRAINT fk_emp_deptno FOREIGN KEY(deptno) REFERENCES dept(deptno)
    ON UPDATE CASCADE ON DELETE RESTRICT
);

INSERT INTO emp1(empno,ename,deptno) VALUES(1234,'king',20);
INSERT INTO emp1(empno,ename,deptno) VALUES(234,'ing',20);
INSERT INTO emp1(empno,ename,deptno) VALUES(34,'ng',20);

UPDATE dept SET deptno=30 WHERE deptno=20;

DELETE FROM dept WHERE deptno=30;

7. 默认值约束和检查约束

7.1 检查约束（Check）

检查某个字段的值是否符合xx要求，一般指的是值的范围

MySQL5.7可以使用check约束，但check约束不生效~

MySQL8.0开始使用check约束，就生效了，可以正确进行检查

• 创建表时添加检查约束，可以直接添加，也可以在后面添加（起别名的方式），删除比较方便

#1
CREATE TABLE stu
(
	 id INT PRIMARY KEY AUTO_INCREMENT,
	 name VARCHAR(10),
	 gender CHAR CHECK(gender IN('男','女'))
);

#2
CREATE TABLE stu
(
	 id INT PRIMARY KEY AUTO_INCREMENT,
	 name VARCHAR(10),
	 gender CHAR CHECK(gender IN('男','女')),
	 age TINYINT,
	 sal DECIMAL(10,2),
	 CONSTRAINT ck_stu_sal CHECK(sal>200),
	 CONSTRAINT ck_stu_age CHECK(age BETWEEN 18 AND 120)
);

• 再举例

age TINYINT check(age>20) 
或
age TINYINT check(age BETWEEN 18 AND 120) 

• 创建表后添加检查约束

ALTER TABLE stu ADD CONSTRAINT ck_stu_sal CHECK(sal>2000);

删除检查约束

ALTER TABLE stu DROP CHECK ck_stu_sal;

7.2 默认值约束(Default)

当为数据库表中插入一条新记录时，如果没有为某个字段赋值，那么数据库系统会自动为这个字段插入默认值。为了达到这种效果，可以通过SQL语句关键字DEFAULT来设置。

设置数据库表中某字段的默认值非常简单，其语法形式如下:

• 创建表时添加

CREATE TABLE df
(
		id INT PRIMARY KEY,
		name VARCHAR(10) DEFAULT 'maye'
);

• 创建表后添加

ALTER TABLE df MODIFY name VARCHAR(10) DEFAULT 'hello';

• 删除默认约束

ALTER TABLE df MODIFY name VARCHAR(10);

8. 综合实战

8.1 建立数据表

8.1.1 表字段描述

到了秋天，为了让同学们增加体育锻炼，所以学校开始筹备学生运动会的活动，为了方便保存比赛成绩信息，所以定义了如下的几张数据表。

• 运动员表（sporter）：
  • 运动员编号sporterno、运动员姓名name、运动员性别gender、所属系号deptno
• 体育项目表（item）：
  • 项目编号itemno、项目名称iname、比赛地点loc
• 成绩表（grade）：
  • 运动员编号sporterno、项目编号itemno、得分mark

在成绩表之中包含了两个外键：分别是运动员的编号、项目的编号，之所以这样设计，是因为一个运动员可以参加多个项目，一个项目可以有多个运动员参加，每个运动员针对不同的项目有自己的成绩。所以运动员和项目之间是属于多对多关系。而之前所学的emp-dept是属于一对多的关系。

8.1.2 表中的约束

8.1.3 SQL语句

# 创建数据库
CREATE DATABASE IF NOT EXISTS games1;
USE games1;

CREATE TABLE IF NOT EXISTS spoter
(
	spoterno INT,
	name     VARCHAR(30) NOT NULL,
	gender   CHAR        NOT NULL,
	deptname VARCHAR(30) NOT NULL,
	CONSTRAINT pk_spoterno PRIMARY key(spoterno),
	CONSTRAINT ck_gender   check(gender IN('男','女'))
)***MENT '运动员表';

CREATE TABLE IF NOT EXISTS item
(
	itemno   INT,
	itemname VARCHAR(30) NOT NULL,
	loc      VARCHAR(30) NOT NULL,
	CONSTRAINT pk_itemno PRIMARY key(itemno)
)***MENT '项目表';

CREATE TABLE IF NOT EXISTS grade
(
	spoterno INT,
	itemno INT,
	mark INT,
	CONSTRAINT ck_mark CHECK(mark IN(0,2,4,6)),
	CONSTRAINT fk_spoterno FOREIGN key(spoterno) REFERENCES spoter(spoterno),
	CONSTRAINT fk_itemno FOREIGN key(itemno) REFERENCES item(itemno)
)***MENT '成绩表';


--  添加测试数据
INSERT INTO spoter(spoterno,name,gender,deptname) VALUES(1001,'rust','男','计算机系');
INSERT INTO spoter(spoterno,name,gender,deptname) VALUES(1002,'go','男','数学系');
INSERT INTO spoter(spoterno,name,gender,deptname) VALUES(1003,'python','男','计算机系');
INSERT INTO spoter(spoterno,name,gender,deptname) VALUES(1004,'c++','男','物理系');
INSERT INTO spoter(spoterno,name,gender,deptname) VALUES(1005,'c','女','心理系');
INSERT INTO spoter(spoterno,name,gender,deptname) VALUES(1006,'java','女','数学系');

INSERT INTO item(itemno,itemname,loc) VALUES(1,'男子五千米','一操场');
INSERT INTO item(itemno,itemname,loc) VALUES(2,'男子标枪','一操场');
INSERT INTO item(itemno,itemname,loc) VALUES(3,'男子跳远','二操场');
INSERT INTO item(itemno,itemname,loc) VALUES(4,'女子跳高','二操场');
INSERT INTO item(itemno,itemname,loc) VALUES(5,'女子三千米','三操场');

INSERT INTO grade(spoterno,itemno,mark) VALUES(1001,1,6);
INSERT INTO grade(spoterno,itemno,mark) VALUES(1002,1,4);
INSERT INTO grade(spoterno,itemno,mark) VALUES(1003,1,2);
INSERT INTO grade(spoterno,itemno,mark) VALUES(1004,1,0);
INSERT INTO grade(spoterno,itemno,mark) VALUES(1001,3,4);
INSERT INTO grade(spoterno,itemno,mark) VALUES(1002,3,6);
INSERT INTO grade(spoterno,itemno,mark) VALUES(1004,3,2);
INSERT INTO grade(spoterno,itemno,mark) VALUES(1003,3,0);
INSERT INTO grade(spoterno,itemno,mark) VALUES(1005,4,6);
INSERT INTO grade(spoterno,itemno,mark) VALUES(1006,4,4);
INSERT INTO grade(spoterno,itemno,mark) VALUES(1001,4,2);
INSERT INTO grade(spoterno,itemno,mark) VALUES(1002,4,0);
INSERT INTO grade(spoterno,itemno,mark) VALUES(1003,2,6);
INSERT INTO grade(spoterno,itemno,mark) VALUES(1005,2,4);
INSERT INTO grade(spoterno,itemno,mark) VALUES(1006,2,2);
INSERT INTO grade(spoterno,itemno,mark) VALUES(1001,2,0);

SELECT * FROM spoter;
SELECT * FROM item;
SELECT * FROM grade;

8.2 数据操作

第一题：求出目前总积分最高的系名及其积分。

1. 确定所需要的数据表
   1. spoeter：系名
   2. grade：积分
2. 确定已知关联字段
   1. 远动员和积分：sporter.sporterno=grade.sporterno

WITH temp AS
(	
	SELECT s.deptname deptname,SUM(g.mark) totalmark
	FROM spoter s,grade g
	WHERE s.spoterno=g.spoterno
	GROUP BY deptname
)
SELECT deptname,totalmark FROM temp
HAVING totalmark=(SELECT MAX(totalmark) FROM temp);

第二题：找出在一操场进行比赛的各项目名称极其冠军的姓名。

1. 确定所需数据表
   #sporter:冠军的姓名
   #item：一操场比赛的运动员
   #grade：求出冠军
2. 确定关联字段
   #运动员和积分：sporter.sporterno=grade.sporterno
   #项目和积分：item.itemno = grade.itemno

WITH temp AS
(	
	SELECT i.itemno,MAX(g.mark) maxmark
	FROM item i,grade g
	WHERE i.itemno=g.itemno AND i.loc='一操场'
	GROUP BY i.itemno
)
SELECT * 
FROM temp t,spoter s,item i,grade g
WHERE g.mark=t.maxmark AND s.spoterno=g.spoterno 
AND i.itemno=g.itemno AND t.itemno=g.itemno;

第三题：找出参加了c++所参加过的项目的其他同学的姓名。
确定所需数据表 ，sporter：c++， grade：c++参加过的项目

WITH temp AS
(
	SELECT g.itemno
	FROM spoter s,grade g
	WHERE s.spoterno=g.spoterno
	AND s.name='c++'
)
SELECT DISTINCT s.name
FROM spoter s,grade g
WHERE s.spoterno=g.spoterno 
AND g.itemno IN(SELECT * FROM temp)
AND s.name!='c++';

第四题：经查c++因为使用了违禁药品，其成绩都记为0分，请在数据库中做出相应的修改。

UPDATE grade SET mark=0 WHERE spoterno=
(SELECT spoterno FROM spoter WHERE name='c++');

第五题：经组委会协商，需要删除女子跳高比赛项目。先删除grade中的外键

ALTER TABLE grade DROP FOREIGN KEY fk_itemno;
DELETE FROM item WHERE itemname='女子跳高';

总结

总的来说，使用约束可以提高数据库的数据质量、一致性和完整性，简化数据操作，并提高数据库的性能。它是数据库设计和管理中的重要概念，有助于保证数据的可靠性和有效性~下节为大家打来数据类型的分享