MySQL 外键的作用:
保持数据一致性, 完整性, 主要目的是控制存储在外键表中的数据. 使两张表形成关联, 外键只能引用外表中列的值!
我们来建两个表
- CREATE TABLE `example1` (`stu_id` int(11) NOT NULL DEFAULT '0',
- `course_id` int(11) NOT NULL DEFAULT '0',
- `grade` float DEFAULT NULL,
- PRIMARY KEY (`stu_id`,`course_id`)
- );
- CREATE TABLE `example2` (
- `id` int(11) NOT NULL,
- `stu_id` int(11) DEFAULT NULL,
- `course_id` int(11) DEFAULT NULL,
- PRIMARY KEY (`id`),
- KEY `f_ck` (`stu_id`,`course_id`),
- CONSTRAINT `f_ck` FOREIGN KEY (`stu_id`, `course_id`) REFERENCES `example1` (`stu_id`, `course_id`)
- );
- insert into example1 (stu_id,course_id,grade)values(1,1,98.5),(2,2,89);
- insert into example2 (id,stu_id,course_id)values(1,1,1),(2,2,2);
我们建了
example1 表, 里面包含 stu_id 学号, course_id 课程号, grade 分数
example2 表, 里面包含 id,stu_id 学号, course_id 课程号, 然后建立外键
分别插入数据到两个表中.
我们把 example2 中的 stu_id 和 course_id 称为 example2 表的外键, example1 是父表, example2 是字表, 两个表形成关联, 必须字表的数据删除后, 才能删除父表中的对应数据
现在我们来删除 example1 中的一条数据
delete from example1 where stu_id=2;
会发现报错
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`example3`, CONSTRAINT `f_ck` FOREIGN KEY (`stu_id`, `course_id`) REFERENCES `example2` (`stu_id`, `course_id`))
因为 example2 中的数据关联了 example1 的数据, 这样是删不了的, 达到了外键的作用;
然后我们来先删除 example2 表中的数据, 再删除 example1 表中的数据
- delete from example2 where stu_id=2;
- delete from example1 where stu_id=2;
这样就成功了;
事件触发限制:
on delete 和 on update , 可设参数 cascade(跟随外键改动), restrict(限制外表中的外键改动),set Null(设空值),set Default(设默认值),[默认]no action
我们来看看事件触发限制是干嘛的...
我们先删除外键, 然后重新建立外键带上事件触发限制
- alter table example2 drop foreign key f_ck;
- alter table example2 add CONSTRAINT `f_ck` FOREIGN KEY (`stu_id`, `course_id`) REFERENCES `example1` (`stu_id`, `course_id`) ON DELETE CASCADE ON UPDATE CASCADE;
我们先查看一下数据
- mysql> select * from example1;select * from example2;
- +--------+-----------+-------+
- | stu_id | course_id | grade |
- +--------+-----------+-------+
- | 1 | 1 | 98.5 |
- +--------+-----------+-------+
- 1 row in set (0.00 sec)
- +----+--------+-----------+
- | id | stu_id | course_id |
- +----+--------+-----------+
- | 1 | 1 | 1 |
- +----+--------+-----------+
- 1 row in set (0.00 sec)
这时 example1 和 example2 中的 stu_id 和 course_id 都是 1,
再来修改 example1 表中的数据看看
update example1 set stu_id=3,course_id=3 where stu_id=1;
再来查看数据
- mysql> select * from example1;select * from example2;
- +--------+-----------+-------+
- | stu_id | course_id | grade |
- +--------+-----------+-------+
- | 3 | 3 | 98.5 |
- +--------+-----------+-------+
- 1 row in set (0.00 sec)
- +----+--------+-----------+
- | id | stu_id | course_id |
- +----+--------+-----------+
- | 1 | 3 | 3 |
- +----+--------+-----------+
- 1 row in set (0.00 sec)
发现没, example1 和 example2 中的 stu_id 和 course_id 都变成了 3
我们在来删除 example1 表中的数据
delete from example1 where stu_id=3;
会发现可以删除, 而且 example2 中的数据也没有了;
其实啊, 外键就这个作用, 保持数据一致性, 完整性, 是不让改还是一起改, 由事件触发器决定;
来源: http://www.linuxidc.com/Linux/2018-04/151970.htm