mysql-外键约束（foreign key）

记录下外键约束的学习

外键（foreign key）
用来保证参照完整性

一般被引用的表称父表，引用的表称子表
如创建学生表，社团表；
社团表的学生来自学生表；
学生表为被引用的表（父表）；

在定义外键时，通过定义on delete/update指定操作父表时对子表的操作

选项	操作父表	子表对应记录
cascade	delete/ update	delete/ update
set null	delete/ update	数据被更新为null
no action	delete/ update	报错不允许操作发生
restrict	delete/ update	报错不允许操作发生

不定义默认restrict

操作
1.创建学生表导入数据，社团表导入数据，定义社团成员（来自学生表）为外键（foreign key）

学生表：

mysql> create table xuesheng(
    -> xs_id int not null auto_increment,
    -> name varchar(30),                                                            -> nianji int(10),
    -> primary key(xs_id));

mysql> select * from xuesheng;
+-------+--------+--------+
| xs_id | name   | nianji |
+-------+--------+--------+
|     1 | name1  |      1 |
|     2 | name2  |      2 |
|     3 | name3  |      3 |
|     4 | name4  |      4 |
|   888 | name88 |      8 |
+-------+--------+--------+
5 rows in set (0.00 sec)

社团表

mysql> create table shetuan(
    -> st_id int not null,
    -> stcy_id int not null comment'社团成员',
    -> stcy_name varchar(30),
    -> st_name varchar(30),
    -> oreign key (stcy_id) references xuesheng(xs_id));
Query OK, 0 rows affected (0.10 sec)

mysql> select * from shetuan;
+-------+---------+-----------+---------+
| st_id | stcy_id | stcy_name | st_name |
+-------+---------+-----------+---------+
|     1 |       1 | name1     | jiewu   |
|     1 |       2 | name2     | wuda    |
|     1 |     888 | name88    | hechang |
+-------+---------+-----------+---------+

2 对学生表（父表）进行更新删除

2.1社团表不设置on delete/on update

更新学生表（父表）id：888为5
结果应是：不能更新：

mysql> update xuesheng set xs_id=5 where xs_id=888;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`practice`.`shetuan`, CONSTRAINT `shetuan_ibfk_1` FOREIGN KEY (`stcy_id`) REFERENCES `xuesheng` (`xs_id`))

更新失败

2.2 设置社团表同步删除更新
on delete cascade on update cascade

mysql>  create table shetuan(
    -> st_id int not null,
    -> stcy_id int not null comment'社团成员',
    -> stcy_name varchar(30),
    -> st_name varchar(30),
    -> foreign key (stcy_id) references xuesheng(xs_id)
    -> on delete cascade on update cascade);
Query OK, 0 rows affected (0.07 sec)

2.2.1 更新学生表（父表）id：888为5
结果应是：
更新ok，同时社团表里的stcy_id也从888 变为 5；

mysql> update xuesheng set xs_id=5 where xs_id=888;   
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from shetuan;
+-------+---------+-----------+---------+
| st_id | stcy_id | stcy_name | st_name |
+-------+---------+-----------+---------+
|     1 |       1 | name1     | jiewu   |
|     1 |       2 | name2     | wuda    |
|     1 |       5 | name88    | hechang |
+-------+---------+-----------+---------+
3 rows in set (0.00 sec)

更新成功

2.2.3 删除学生表（父表）id 为5记录
结果应是：
删除ok，同时删除社团表id 为 5记录；

mysql> delete from xuesheng where xs_id=5;
Query OK, 1 row affected (0.06 sec)

mysql> select * from shetuan;
+-------+---------+-----------+---------+
| st_id | stcy_id | stcy_name | st_name |
+-------+---------+-----------+---------+
|     1 |       1 | name1     | jiewu   |
|     1 |       2 | name2     | wuda    |
+-------+---------+-----------+---------+
2 rows in set (0.00 sec)

id为5的记录被删除了

2.2.4 社团表中插入的记录在学生表中不存在时，
也是不被允许的(id:99 的成员不在学生表）

mysql> insert into shetuan values(1,99,'name99','jiewu');

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`practice`.`shetuan`, CONSTRAINT `shetuan_ibfk_1` FOREIGN KEY (`stcy_id`) REFERENCES `xuesheng` (`xs_id`) ON DELETE CASCADE ON UPDATE CASCA

插入失败

3.myisam和innodb对比

myisam不支持，对外键的约束只起注释作用，show create table时不显示外键
innodb支持，在定义外键时会自动为外键加上索引

上面并没有对社团表创建索引，innodb会自动加一个索引，可用show create table查看；

mysql> show create table shetuan;
KEY `stcy_id` (`stcy_id`)(该字段加了索引)

4.当某个表被其他表创建了外键参照时，
那么该表的对应索引或者主键禁止被删除；

5 mysql外键检查是即时检查
导入数据时，外键约束往往会花费大量时间，
可在执行load data和alter table时关闭外键检查，
执行完再开启；
set foreign_key_checks=0;
load data/alter table
set foreign_key_checks=1;

小白冲鸭！！