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

公众号guangcity

发布于 2019-09-20 17:31:41

4.3K00

代码可运行

文章被收录于专栏：光城(guangcity)光城(guangcity)

运行总次数：0

代码可运行

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，不能删除或更新父表数据。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2019-06-28，如有侵权请联系 cloudcommunity@tencent.com 删除

云数据库 SQL Server