innodb中外键的简单讲解

此博文内容通俗易懂，还结合了实例，适合我这样的小白阅读（汗！）

参考链接：

https://blog.csdn.net/weixin_42102152/article/details/80143519

1.新建一个数据库。

mysql>create database school;

2.新建一个班级表 班级名称（name)，

mysql>create table sc_class(id smallint primary key,

                                                     name varchr(30)，

  )

mysql>insert into sc_class(name) VALUES('chinese'),('math'),('english');

3.新建一个学生表 名字（name） 学号（number）class_id 对应的班级 连接到补习班级表（外链）id。

mysql>create table student( id smallint auto_increment primary key, name varchar(20) not null, number int(20) not null,class_id int, FOREIGN KEY(class_id) REFERENCES sc_class(id));

* 这里学生表中的class_id 就是外键 连接到 补习班表中

1.什么是外键? 有什么作用？

外键的定义：表的外键是另一张表的主键。将两张表联系到一起。

作用：简单的说是为了保证数据的完整性。

2.外键的要求？

 

1. 父表（student）与子表(sc_class)必须具有相同的存储引擎，而且禁用使用临时表
2. 数据表的存储引擎必须为InnoDB
3. 外键列与参照列必须具有相似的数据类型，其中数字的长度或有无符号位必须相同，而字符的长度可以不同。
4. 外键列和参照列必须创建索引，如果外键列不存在索引，mysql会自动创建索引。

外键约束

在使用外键的过程中，还需要了解到什么是外键约束，顾名思义，就是使用外键的一系列规则，目的是为了保证数据的完整性和一致性，只是这个规则需要自己去定义。

那么怎么操作和定义这个规则呢？先来记住这几个关键字：

 

1. CASCADE：从父表删除或更新会自动删除或更新子表中匹配的行
2. SET NULL：从父表删除或更新行，会设置子表中的外键列为NULL，但必须保证子表列没有指定NOT NULL
3. RESTRICT：拒绝对父表的删除或更新操作（如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作，这个是ANSI SQL-92标准,从mysql4.0.8开始支持）
4. NO ACTION：标准SQL的关键字，在mysql中与RESTRICT相同

* 子表指的是外键所在的表，这里是student。父表是外键所连接的表这里是sc_class.

测试一下没有约束前删出父表的内容会怎么样：

mysql> select * from student;
+----+------+--------+----------+
| id | name | number | class_id |
+----+------+--------+----------+
|  1 | jack |   1001 |        1 |
|  2 | jack |   1002 |        2 |
|  3 | alex |   1003 |        2 |
+----+------+--------+----------+
3 rows in set (0.00 sec)

mysql> select * from sc_class;
+----+---------+
| id | name    |
+----+---------+
|  1 | english |
|  2 | chinese |
|  3 | math    |
+----+---------+

3 rows in set (0.00 sec)

1.这里可以看到没有设置约束条件时是删除不了的。

mysql> delete from sc_class where id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`school`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `sc_class` (`id`))

2.修改约素为CASCADE。

mysql> alter table student DROP FOREIGN KEY student_ibfk_1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table student add CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `sc_class` (`id`)  ON DELETE CASCADE;
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

 

3.再查看现在表是否有变化？

mysql> delete from sc_class where id=1;
Query OK, 1 row affected (0.00 sec)

mysql> select * from sc_class;
+----+---------+
| id | name    |
+----+---------+
|  2 | chinese |
|  3 | math    |
+----+---------+
2 rows in set (0.00 sec)

mysql> select * from student;
+----+------+--------+----------+
| id | name | number | class_id |
+----+------+--------+----------+
|  2 | jack |   1002 |        2 |
|  3 | alex |   1003 |        2 |
+----+------+--------+----------+
2 rows in set (0.00 sec)

 

这里就可以看到不仅父表被删除了 连子表匹配的项也被删除了。