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;

⑩

本网站所收集的部分公开资料来源于AI生成和互联网，转载的目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。