MySQL实战七：你不知道的外键与约束使用！

2019 年 10 月 6 日
筆記

MySQL学习仓库Up-Up-MySQL，这是一个学习MySQL从入门实战到理论完善，再到精通的一个仓库，后面会把MySQL的学习资料上传上去！欢迎大家star与fork起来！

仓库地址：

https://github.com/Light-City/Up-Up-MySQL

也可以点击阅读原文！

今天上手第七弹，DDL及check约束无效，你知造!

1.标准的SQL语句

DML（Data Manipulation Language，数据操作语言）语句：主要由select、insert、update和 delete 四个关键字完成。

DDL（Data Definition Language，数据定义语言）语句：主要由create、alter、drop和 truncate 四个关键字完成。

DCL（Data Control Language，数据控制语言）语句：主要grant和revoke 两个关键字完成。

事务控制语句：主要由commit、rollback和savepoint 三个关键字完成。

2.DDL

2.1 check约束无效，如何解决？

定义学生表，练习check、primary key 、enum。

CREATE TABLE Student (      SId varchar(7),      Sname varchar(10) NOT NULL,      Ssex varchar(4) CHECK (Ssex = '男'      OR Ssex = '女'),      Sage int CHECK (Sage >= 15      AND Sage <= 45),      Sdept varchar(20) DEFAULT '计算机系',      PRIMARY KEY (SId)  );

当往里面插入数据的时候，发现check约束不起作用！如下所示：

mysql> insert into Student values('01','赵雷','男1',70,'1');  Query OK, 1 row affected (0.10 sec)    mysql> select * from Student;  +-----+--------+------+------+-------+  | SId | Sname  | Ssex | Sage | Sdept |  +-----+--------+------+------+-------+  | 01  | 赵雷   | 男1  |   70 | 1     |  +-----+--------+------+------+-------+  1 row in set (0.00 sec)

此时查看表定义：

show create table Student

果不其然，没得check约束：mysql是不支持check约束的。如果你创建表的时候加上了check约束也是不起作用的。所以,你不用更改或删除之前的check约束。

| Student | CREATE TABLE `Student` (    `SId` varchar(7) NOT NULL,    `Sname` varchar(10) NOT NULL,    `Ssex` varchar(4) DEFAULT NULL,    `Sage` int(11) DEFAULT NULL,    `Sdept` varchar(20) DEFAULT '计算机系',    PRIMARY KEY (`SId`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8     |

使用enum限制插入的值

所以为了完成性别这种的离散范围，可以使用enum，此时我们对上述表进行修改：

mysql> alter table Student modify column Ssex enum('男','女');  ERROR 1265 (01000): Data truncated for column 'Ssex' at row 1

上面提示，需要truncate data，由于之前插入的数据不符合要求，所以必须先：

mysql> truncate table Student;  Query OK, 0 rows affected (0.25 sec)

再进行修改：

mysql> alter table Student modify column Ssex enum('男','女');  Query OK, 0 rows affected (0.62 sec)  Records: 0  Duplicates: 0  Warnings: 0  mysql> insert into Student values('01','赵雷','男1',70,'1');  ERROR 1265 (01000): Data truncated for column 'Ssex' at row 1  mysql> insert into Student values('01','赵雷','男',70,'1');  Query OK, 1 row affected (0.18 sec)

此时，再次插入错误数据，就会提示不满足列要求。

现在来验证一下这个表结构是否修改：

mysql> show create table Student;  | Student | CREATE TABLE `Student` (    `SId` varchar(7) NOT NULL,    `Sname` varchar(10) NOT NULL,    `Ssex` enum('男','女') DEFAULT NULL,    `Sage` int(11) DEFAULT NULL,    `Sdept` varchar(20) DEFAULT '计算机系',    PRIMARY KEY (`SId`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8       |

会发现，enum已经有了！表结构修改成功！

但是问题又来了，上述方法对于离散数据没问题，可是对于范围的连续数据呢？比如上述的年龄字段，本来想约束限制为15到45的，结果发现70竟然可以插入，这肯定不符合要求！

那么如何解决呢，下面一起来看！

触发器解决限制范围内数据

创建触发器：

delimiter $$  create trigger age_check before insert on Student  for each row  begin      declare msg varchar(100);      if new.Sage<15 or new.Sage>45 then set msg=concat('您输入的年龄值：',new.Sage,'为无效的年龄，请输入15到45以内的有效数字。');      signal sqlstate 'HY000' set message_text=msg;  end if;  end;  $$  delimiter ;

delimiter解释：其实就是告诉mysql解释器，该段命令是否已经结束了，mysql是否可以执行了。默认情况下，delimiter是分号(😉 。但有时候，不希望MySQL这么做。在为可能输入较多的语句，且语句中包含有分号。

上述就是先修改语句执行当碰到$$时候再去执行，然后复原;号。

mysql> insert into Student values('02','赵雷','男',70,'1');  ERROR 1644 (HY000): 您输入的年龄值：70为无效的年龄，请输入15到45以内的有效数字。  mysql> insert into Student values('02','赵雷','男',20,'1');  Query OK, 1 row affected (0.10 sec)

第一次插入不符合要求的年龄，发现确实提示报错了！证明check约束起作用了，当插入范围内数据，可以看到插入成功！

2.2 外键

2.2.1 创建外键

（1）不带别名的外键,数据库自动生成

首先创建department表：

CREATE TABLE department (      dept_name varchar(20) PRIMARY KEY NOT NULL,      building varchar(20),      budget int  );

其次，创建instructor表：

CREATE TABLE instructor (      ID char(5),      name varchar(20) NOT NULL,      dept_name varchar(20),      salary numeric(8, 2),      PRIMARY KEY (ID),      FOREIGN KEY (dept_name) REFERENCES department (dept_name)  );

查看生成的外键名：

show create table instructor    | instructor | CREATE TABLE `instructor` (    `ID` char(5) NOT NULL,    `name` varchar(20) NOT NULL,    `dept_name` varchar(20) DEFAULT NULL,    `salary` decimal(8,2) DEFAULT NULL,    PRIMARY KEY (`ID`),    KEY `dept_name` (`dept_name`),    CONSTRAINT `instructor_ibfk_1` FOREIGN KEY (`dept_name`) REFERENCES `department` (`dept_name`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

可以看到是instructor_ibfk_1。

（2）带别名的外键

CREATE TABLE instructor (      ID char(5),      name varchar(20) NOT NULL,      dept_name varchar(20),      salary numeric(8, 2),      PRIMARY KEY (ID),      CONSTRAINT fk_dept_name FOREIGN KEY (dept_name) REFERENCES department (dept_name)  );

查看生成的外键名：

show create table instructor    | instructor | CREATE TABLE `instructor` (    `ID` char(5) NOT NULL,    `name` varchar(20) NOT NULL,    `dept_name` varchar(20) DEFAULT NULL,    `salary` decimal(8,2) DEFAULT NULL,    PRIMARY KEY (`ID`),    KEY `fk_dept_name` (`dept_name`),    CONSTRAINT `fk_dept_name` FOREIGN KEY (`dept_name`) REFERENCES `department` (`dept_name`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

可以看到是fk_dept_name。

（2）删除外键

alter table instructor drop foreign key fk_dept_name;

（3）添加外键

alter table instructor add constraint fk_dept_name foreign key(dept_name) references department(dept_name);

2.2.2 on update和on delete

这是数据库外键定义的一个可选项，用来设置当主键表中的被参考列的数据发生变化时，外键表中响应字段的变换规则的。update 则是主键表中被参考字段的值更新，delete是指在主键表中删除一条记录： on update 和 on delete 后面可以跟的词语有四个:no action ， set null ， set default ，cascade。 no action 表示不做任何操作， set null 表示在外键表中将相应字段设置为null set default 表示设置为默认值(restrict)

（1）on delete cascade学习

cascade 表示级联操作，就是说，如果主键表中被参考字段更新，外键表(子表)中也更新，主键表(父表)中的记录被删除，外键表(子表)中改行也相应删除。

还是上述例子，首先创建instructor与department表。

创表

父表：

CREATE TABLE department (      dept_name varchar(20) PRIMARY KEY NOT NULL,      building varchar(20),      budget int  );

子表：

CREATE TABLE instructor (      ID char(5),      name varchar(20) NOT NULL,      dept_name varchar(20),      salary numeric(8, 2),      PRIMARY KEY (ID),      CONSTRAINT fk_dept_name FOREIGN KEY (dept_name) REFERENCES department (dept_name)      ON UPDATE cascade  );

插入数据

当父表无数据，直接向子表插入数据，报错

mysql> insert into instructor values(1,'小米','小米手机部门',1000.2);  ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`ddl`.`instructor`, CONSTRAINT `fk_dept_name` FOREIGN KEY (`dept_name`) REFERENCES `department` (`dept_name`) ON DELETE CASCADE)

不管有没有on delete cascade都会出现这个问题。

现在往先往父表中插入数据：

mysql> insert into department values('小米手机部门','北京',10000);  Query OK, 1 row affected (0.07 sec)

往从表插入数据：

mysql> insert into instructor values(1,'小米','小米手机部门',1000.2);  Query OK, 1 row affected (0.08 sec)

查看两表数据：

mysql> select * from department;  +--------------------+----------+--------+  | dept_name          | building | budget |  +--------------------+----------+--------+  | 小米手机部门       | 北京     |  10000 |  +--------------------+----------+--------+  1 row in set (0.00 sec)    mysql> select * from instructor;  +----+--------+--------------------+---------+  | ID | name   | dept_name          | salary  |  +----+--------+--------------------+---------+  | 1  | 小米   | 小米手机部门       | 1000.20 |  +----+--------+--------------------+---------+  1 row in set (0.00 sec)

删除

父子表都有数据，删除子表数据

mysql> delete from instructor where dept_name='小米手机部门';  Query OK, 1 row affected (0.13 sec)    mysql> select * from instructor;  Empty set (0.00 sec)    mysql> select * from department;  +--------------------+----------+--------+  | dept_name          | building | budget |  +--------------------+----------+--------+  | 小米手机部门       | 北京     |  10000 |  +--------------------+----------+--------+  1 row in set (0.01 sec)

发现子表数据被删除，父表数据不变。

父子表都有数据，删除父表数据

mysql> select * from instructor;  +----+--------+--------------------+---------+  | ID | name   | dept_name          | salary  |  +----+--------+--------------------+---------+  | 1  | 小米   | 小米手机部门       | 1000.20 |  +----+--------+--------------------+---------+  1 row in set (0.00 sec)    mysql> select * from department;  +--------------------+----------+--------+  | dept_name          | building | budget |  +--------------------+----------+--------+  | 小米手机部门       | 北京     |  10000 |  +--------------------+----------+--------+  1 row in set (0.00 sec)    mysql> delete from department where dept_name='小米手机部门';  Query OK, 1 row affected (0.10 sec)    mysql> select * from department;  Empty set (0.00 sec)    mysql> select * from instructor;  Empty set (0.00 sec)

父表数据被删除，子表联带被删除！

如果没有使用`on delete/update cascade`，不能删除或更新父表数据，当删除父表的数据时候报错！如下所示：

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`ddl`.`instructor`, CONSTRAINT `instructor_ibfk_1` FOREIGN KEY (`dept_name`) REFERENCES `department` (`dept_name`))

要想删除，必须先子表再父表！如下所示：

mysql> delete from instructor where dept_name='小米手机部门';  Query OK, 1 row affected (0.15 sec)    mysql> select * from instructor;  Empty set (0.00 sec)    mysql> select * from department;  +--------------------+----------+--------+  | dept_name          | building | budget |  +--------------------+----------+--------+  | 小米手机部门       | 北京     |  10000 |  +--------------------+----------+--------+  1 row in set (0.00 sec)    mysql> delete from department where dept_name='小米手机部门';  Query OK, 1 row affected (0.11 sec)    mysql> select * from department;  Empty set (0.00 sec)

父子表都有数据，更新父表主键或者子表外键，都无效

如果想要更新，那么就必须换成on update cascade。

上述on delete cascade换成on update cascade，可以发现只能更新父表的主键，同时父子表数据都会被更新，但是在子表的外键上做更新操作无效！

mysql> update departme set dept_name='苹果手机部门' where dept_name='小米手机部门'  Query OK, 1 row affected (0.12 sec)  Rows matched: 1  Changed: 1  Warnings: 0    mysql> select * from department;  +--------------------+----------+--------+  | dept_name          | building | budget |  +--------------------+----------+--------+  | 苹果手机部门       | 北京     |  10000 |  +--------------------+----------+--------+  1 row in set (0.00 sec)    mysql> select * from instructor;  +----+--------+--------------------+---------+  | ID | name   | dept_name          | salary  |  +----+--------+--------------------+---------+  | 1  | 小米   | 苹果手机部门       | 1000.20 |  +----+--------+--------------------+---------+  1 row in set (0.00 sec)    mysql> update instructor set dept_name='小米手机部门' where dept_name='苹果手机 部门';  Query OK, 0 rows affected (0.00 sec)  Rows matched: 0  Changed: 0  Warnings: 0    mysql> select * from instructor;  +----+--------+--------------------+---------+  | ID | name   | dept_name          | salary  |  +----+--------+--------------------+---------+  | 1  | 小米   | 苹果手机部门       | 1000.20 |  +----+--------+--------------------+---------+  1 row in set (0.00 sec)    mysql> select * from department;  +--------------------+----------+--------+  | dept_name          | building | budget |  +--------------------+----------+--------+  | 苹果手机部门       | 北京     |  10000 |  +--------------------+----------+--------+  1 row in set (0.00 sec)

2.2.3 总结

on delete cascade 不能更新父表主键或子表外键，删除父表主键数据会将子表联同删除，删除子表外键数据不影响父表。而on update只能删除子表外键数据，不能删除父表主键数据，只能更新父表的主键，同时父子表数据都会被更新，但是在子表的外键上做更新操作无效。如果没有使用on delete/update cascade，不能删除或更新父表数据。