MySQL外键约束

什么是外检约束

外键其实很好理解,简单的说就是两张表建立一个连接关系。这里我们那主表A和副表B举例,我A表中有用户信息,B表中有用户订单信息。要是数据完整对应起来,肯定是需要把两张表关联起来,我们因此会在B表中村一个A表的字段,常见的我们存的是A表的主键ID外键。

外检约束要求

.MySQL的数据表存储引擎必须为Innodb。 .主表和副表关联的字段数据类型的一致。 .字段不能设置为NULL。 .主表中的字段需为主键。

外键约束的作用

保证数据的完整性和一致性.

创建语法

[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name, ...)
REFERENCES tbl_name (index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION
 1. CASCADE: 从父表中删除或更新对应的行,同时自动的删除或更新自表中匹配的行。ON DELETE CANSCADE和ON UPDATE CANSCADE都被InnoDB所支持。
 2. SET NULL: 从父表中删除或更新对应的行,同时将子表中的外键列设为空。注意,这些在外键列没有被设为NOT NULL时才有效。ON DELETE SET NULL和ON UPDATE SET SET NULL都被InnoDB所支持。
 3. NO ACTION: InnoDB拒绝删除或者更新父表。
 4. RESTRICT: 拒绝删除或者更新父表。指定RESTRICT(或者NO ACTION)和忽略ON DELETE或者ON UPDATE选项的效果是一样的。
 5. SET DEFAULT: InnoDB目前不支持。

实战演示

// 主表
mysql> create table userInfo(
    -> id int(4) not null primary key auto_increment,
    -> nick_name varchar(10) not null,
    -> age int(3) default 1)engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.07 sec)

insert into userInfo(`nick_name`, `age`) value ('user001',12),('user002',13);
// 从表
mysql> create table orders(
    -> id int(11) not null primary key auto_increment,
    -> u_id int(4) not null,
    -> number varchar(32) not null)engine=innodb default charset=utf8;                    Query OK, 0 rows affected (0.03 sec)

insert into orders(`u_id`,`number`) value(1,'1234567890'),(2,'0987654321');

.CASCADE情况

alter table orders add constraint foreign key `f_uid` (`u_id`) references userInfo(`id`) on delete  cascade on update cascade;
// 执行删除主表数据
mysql> select * from userInfo;
+----+-----------+------+
| id | nick_name | age  |
+----+-----------+------+
|  1 | user001   |   12 |
|  2 | user002   |   13 |
+----+-----------+------+
2 rows in set (0.01 sec)

mysql> select * from orders;
+----+------+------------+
| id | u_id | number     |
+----+------+------------+
|  1 |    1 | 1234567890 |
|  2 |    2 | 0987654321 |
+----+------+------------+
2 rows in set (0.00 sec)

mysql> delete from userInfo where id=2;
Query OK, 1 row affected (0.01 sec)

mysql> select * from userInfo;
+----+-----------+------+
| id | nick_name | age  |
+----+-----------+------+
|  1 | user001   |   12 |
+----+-----------+------+
1 row in set (0.01 sec)

mysql> select * from orders;
+----+------+------------+
| id | u_id | number     |
+----+------+------------+
|  1 |    1 | 1234567890 |
+----+------+------------+
1 row in set (0.00 sec)

结论:从上面可以看出当情况为CASCADE的情况时,这时候只要删除主表,从表相互关联的数据会自动删除。同理推出update的时候也会自动更新从表中的数据。

. NOACTION情况

alter table orders add foreign key `f_uid` (`u_id`) references userInfo(`id`) on delete  no action  on update no action;

mysql> select * from userInfo;
+----+-----------+------+
| id | nick_name | age  |
+----+-----------+------+
|  1 | user001   |   12 |
|  2 | user002   |   13 |
+----+-----------+------+
2 rows in set (0.00 sec)

mysql> select * from orders;
+----+------+------------+
| id | u_id | number     |
+----+------+------------+
|  1 |    1 | 1234567890 |
|  2 |    2 | 0987654321 |
+----+------+------------+
2 rows in set (0.00 sec)

mysql> delete from userInfo where id =1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`u_id`) REFERENCES `userInfo` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)
mysql> delete from orders where id =1;
Query OK, 1 row affected (0.01 sec)

结论:当我们进行删除操作的时候,会提示无法删除或者更新主表。

.RESTRICT

mysql> alter table orders add foreign key `f_uid` (`u_id`) references userInfo(`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;
Query OK, 2 rows affected (0.11 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from orders;
+----+------+------------+
| id | u_id | number     |
+----+------+------------+
|  1 |    1 | 1234567890 |
|  2 |    2 | 0987654321 |
+----+------+------------+
2 rows in set (0.00 sec)

mysql> select * from userInfo;
+----+-----------+------+
| id | nick_name | age  |
+----+-----------+------+
|  1 | user001   |   12 |
|  2 | user002   |   13 |
+----+-----------+------+
2 rows in set (0.00 sec)

mysql> delete from userInfo where id =1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`u_id`) REFERENCES `userInfo` (`id`))
mysql> delete from orders where id =1;
Query OK, 1 row affected (0.01 sec)

总结:这种情况忽略ON DELETE、ON UPDATE和NO ACTION选项的效果是一样的。

.SET DEFAULT情况

mysql> alter table orders add foreign key `f_uid` (`u_id`) references userInfo(`id`) ON DELETE set default ON UPDATE set default;
ERROR 1215 (HY000): Cannot add foreign key constraint

结论:Innodb存储引擎暂不支持。

.SET NULL情况

mysql> alter table orders add foreign key `f_uid` (`u_id`) references userInfo(`id`) ON DELETE set null ON UPDATE set null;
ERROR 1215 (HY000): Cannot add foreign key constraint

结论:Innodb存储引擎暂不支持。

本文分享自微信公众号 - 卡二条的技术圈(gh_0275b8ba153f),作者:浪子编程走四方

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-07-31

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • ThinkPHP自定义验证类规则

    在使用ThinkPHP的过程中,发现这样一个问题。有时候我们要验证一个参数的格式是否正确,然而ThinkPHP内置的验证规则却没有,那只有去自定义函数了。今日在...

    卡二条的技术圈子
  • 如何实现服务器免密码登录

    Mac可以直接打开终端,windows建议安装git shell 然后执行以下命令,一路回车

    卡二条的技术圈子
  • PHP实现无限极分类

    卡二条的技术圈子
  • mysql使用基础 sql语句与数据完整性(二)

    二、DML:Data Manipulation Language 数据操作语言   作用:操作表中的数据的。   关键:INSERT UPDATE DELE...

    欠扁的小篮子
  • MyBatis学习总结(五)——实现关联表查询

    一枝花算不算浪漫
  • TP踩过的坑[修改功能]前台要给input加上隐藏属性

    简单、
  • MyBatis 实现关联表查询

    一、一对一关联  1.1、提出需求   根据班级id查询班级信息(带老师的信息) 1.2、创建表和数据   创建一张教师表和班级表,这里我们假设一个老师只负责教...

    庞小明
  • 第26天:js-$id函数、焦点事件

    一、函数return语句 定义函数的返回值,在函数内部用return来设置返回值,一个函数只能有一个返回值。同时,终止代码的执行。 所有自定义函数默认没有返回值...

    半指温柔乐
  • Mybatid关联表查询

    一、一对一关联  1.1、提出需求   根据班级id查询班级信息(带老师的信息) 1.2、创建表和数据   创建一张教师表和班级表,这里我们假设一个老师只负...

    汤高
  • react获取当前页面的url参数

    react获取当前页面的url参数,必须在url路由对应的组件上获取,在子组件上获取不到,为undefined,获取形如  /news/:id  的后面的参数 ...

    蓓蕾心晴

扫码关注云+社区

领取腾讯云代金券