Mysql基础（五）外键约束

一  外键

激励： 每天进步'一点点'即可

①  思考

1、在MySQL中,我们知道'主键 PRIMARY KEY'的主要作用是'唯一'区分表中的各个'行 [记录]';

思考：但是对于'外键 foreign key'比较陌生?  那么外键'作用'以及'限制'条件和'目的'呢?

②  外键的定义

1、外键是'某个表 A'中的'一列 column',它包含在'另一个表 B'的'主键'中

2、外键也是'索引'的一种,是通过'一张表中的一列'指向'另一张表中的主键',来对两张表进行'关联'

3、一张表可以'有一个'外键,也可以存在'多个外键',与'多张表'进行关联

③  外键的定义

1、外键的'主要作用': 保证数据的'一致性'和'完整性',并且减少数据冗余

2、主要体现在以下'两个'方面：

  (1) 阻止执行

  从表'插入'新行,其'外键值'不是主表的主键值,便阻止插入

  从表'修改'外键值,新值不是主表的主键值便'阻止'修改

  主表'删除'行,其主键值'在从表里存在'便阻止删除(要想删除，必须'先删除从表'的相关行)

  主表'修改'主键值,旧值在'从表里存在'便阻止修改(要想修改，必须'先删除从表'的相关行)

 (2) '级联 cascade'执行

主表'删除'行,连带从表的相关行'一起删除'

主表'修改'主键值,连带从表相关行的'外键值'一起修改

遗留： 关于'主表'和'从表'的概念,一会'通过'案例讲解

思考： 什么是'外键约束'?

④  外键创建限制

1、'父表'必须'已经存在'于数据库中,或者是当前正在创建的表。

如果是后一种情况，则父表与子表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。

2、必须为'父表'定义'主键'

3、外键中'列的数目'必须和父表的主键中'列的数目'相同

4、两张表必须'是 InnoDB 表',MyISAM 表暂时'不支持'外键

备注： 并且它们'没有临时表'

5、外键列必须'建立了索引',MySQL 4.1.2+ 以后的版本在'建立外键时'会'自动'创建索引

6、外键关系的'两个表的列'必须是'数据类型相似',也就是可以'相互转换类型'的'列'

  比如: int 和tinyint 可以,而 int 和 char 则'不可以'

四种方式指定外键

⑤  外键创建1

1、在'已有的表'中增加'外键'

内容来源： help alter table \G

synax '语法':

ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY(外键字段名)
REFERENCES 外表表名(主键字段名)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

重点： 'on delete' | 'on update' 的理解

通俗： 'A 表 [主表]'的 'id [主键]' 是 'B 表 [从表]' 外键

一、on delete

1. on delete cascade： 删除 A 表的数据时,对应 B 表的数据也会'极联删除'

2. on delete set null：删除 A 表的数据时,对应 B 表的'外键字段'置为'空'

   备注： 前提是该字段可以为'空'

如果添加外键时'没加上面内容',删除 A 表的数据时，如果对应 B 表有数据就会报错

二、on update

1. on update cascade： '更新' A 表数据的 id 时,对应 B 表的外键字段也会更新

2. on update set null：'更新' A 表数据的 id 时，对应 B 表的外键字段置为空

   备注： 前提是该字段可以为'空'

如果添加外键时'没加'上面内容,'更新 A 表的 id',如果对应 B 表有数据'就会报错'

三、添加'外键'语句

建表'以后'添加外键：

语法：alter table 表名称 add foreign key (列名称) references 关联表名称（列名称）;

 alter table user add constraint '自定义约束名' foreign key (role_id) \

    references role(id) on delete cascade on update cascade;

⑥  外键创建2

1、在'创建表'时创建'外键'

  [1]、创建'主表'

CREATE TABLE student
(
	id int (11) primary key auto_increment,       //'主键'
	name char(255),
    sex char(255),
	age int(11)
) charset utf8;
 
  [2]、创建'从表'

CREATE TABLE student_score
(
	id int (11) primary key auto_increment,
	class char(255),
    score char(255),
	student_id int(11),                             //实际'外键'
    foreign	key (student_id) REFERENCES student(id) //从表'外键'与主表'主键'关联 
) charset utf8;

说明： 默认 'ENGINE=InnoDB'

2、查看'表结构'

SHOW CREATE TABLE student_score;

说明： 下面基于上述'主表和从表'验证外键作用

⑦  触发限制使用默认值 RESTRICT 的情况下

1、数据'初始化'  --> 注意'先后'顺序

  [1]、先向'主表'中添加'数据'

  [2]、再向'从表'中添加'数据'

现象： 数据'插入'正常,'无报错'、'警告'信息

说明： 以下'相关测试'是在说明'外键约束'

2、'从表' insert 插入'新行',外键值'不在'主表中,'被阻止'

INSERT INTO student_score (class, score, student_id) VALUES('语文', '100', 2);

通俗理解： 不能'引用'不存在的东西

现象： '从表'插入数据'报错'

错误： Cannot add or update a child row: a foreign key constraint fails

3、'从表' update 修改'外键值','新值'不是'主表的主键值',阻止修改

测试语句： UPDATE student_score SET student_id = 2 WHERE student_id = 1;

通俗理解： 不能'引用'不存在的东西

提示： 要想删除，必须'先删除从表'的'相关行'

现象： 修改'从表'数据'报错'

错误： Cannot add or update a child row: a foreign key constraint fails

4、主表 delete '删除行',其'主键值'在'从表'里,存在便阻止删除

通俗理解： 不能删除'还在引用'的东西

提示： 要想删除,必须'先删除'从表的'相关行'

5、主表 update 修改'主键值','旧值'在'从表'里存在,便'阻止'修改

通俗理解： 不能修改'还在引用'的东西

提示：要想'修改',必须'先删除从表'的'相关行[引用该旧值]'

⑧  更改事件触发限制为 CASCADE

1、'初始化'

  [1]、删除'旧的外键'

  ALTER TABLE 表名称 DROP FOREIGN KEY 外键名;
  
  [2]、添加'新的外键',修改事件'触发限制'为 CASCADE

  ALTER TABLE student_score 
  ADD CONSTRAINT s_id FOREIGN KEY (student_id) REFERENCES student (id) 
  ON DELETE CASCADE ON UPDATE CASCADE;

2、此时当'主表'修改'主键值','从表'中'相关行的外键值'将'一起级联修改'

3、如果'主表删除行',从表中的'相关行'将一起被删除

说明： 在'前面'的基础上继续操作

好处： 避免有'脏数据'残留

⑨  小结

事件触发'限制条件'的不同,会造成两张表中的'操作限制'不同

默认是： ON DELETE RESTRICT  ON UPDATE RESTRICT;

⑩