[转]https://blog.csdn.net/weiguang102/article/details/126409406
阅读目录
前言
一、外键作用及其限制条件
1 外键的定义
2 外键的作用
3 外键创建限制
二、外键创建方法
1 创建外键的语法
2 举例
（1）创建两张表
（2）创建外键
（3）查看表结构
三、验证外键作用
1 先向主表中添加数据
2 触发限制使用默认值 RESTRICT 的情况下
（1）从表插入新行，外键值不在主表中，被阻止
（2）从表修改外键值，新值不是主表的主键值，阻止修改
（3）主表删除行，其主键值在从表里存在便阻止删除(要想删除，必须先删除从表的相关行)
（4）主表修改主键值，旧值在从表里存在便阻止修改(要想修改，必须先删除从表的相关行)
3 更改事件触发限制为 CASCADE
（1）查看表结构
（2）查看此时两表中的数据
（3）此时，当主表修改主键值，从表中相关行的外键值将一起修改
（4）如果主表删除行，从表中的相关行将一起被删除
4 结论
四、删除外键约束
前言
在MySQL中，我们都对主键比较了解，知道主键的主要作用是唯一区分表中的各个行；
但是，对于外键（foreign key） 比较陌生。
一、外键作用及其限制条件
1 外键的定义
外键是某个表中的一列，它包含在另一个表的主键中。
外键也是索引的一种，是通过一张表中的一列指向另一张表中的主键，来对两张表进行关联。
一张表可以有一个外键，也可以存在多个外键，与多张表进行关联。
2 外键的作用
外键的主要作用是保证数据的一致性和完整性，并且减少数据冗余。
主要体现在以下两个方面：
阻止执行
从表插入新行，其外键值不是主表的主键值便阻止插入。
从表修改外键值，新值不是主表的主键值便阻止修改。
主表删除行，其主键值在从表里存在便阻止删除(要想删除，必须先删除从表的相关行)。
主表修改主键值，旧值在从表里存在便阻止修改(要想修改，必须先删除从表的相关行)。
级联执行
主表删除行，连带从表的相关行一起删除。
主表修改主键值，连带从表相关行的外键值一起修改。
3 外键创建限制
父表必须已经存在于数据库中，或者是当前正在创建的表。
如果是后一种情况，则父表与子表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。
必须为父表定义主键。
外键中列的数目必须和父表的主键中列的数目相同。
两个表必须是 InnoDB 表，MyISAM 表暂时不支持外键。
外键列必须建立了索引，MySQL 4.1.2 以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显式建立。
外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如 int 和tinyint 可以，而 int 和 char 则不可以；
二、外键创建方法
可以在创建表时创建外键，也可以在已有的表中增加外键。
我们主要讲第二种方式创建外键。
1 创建外键的语法
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}]
1
2
3
4
其中，ON DELETE 和 ON UPDATE 表示事件触发限制，各参数意义如下：
参数 意义
RESTRICT 限制外表中的外键改动（默认值，也是最安全的设置）
CASCADE 跟随外键改动
SET NULL 设为null值
NO ACTION 无动作
SET DEFAULT 设为默认值
2 举例
（1）创建两张表
CREATE TABLE student
(
id int (11) primary key auto_increment,
name char(255),sex char(255),
age int(11)
)charset utf8;
CREATE TABLE student_score
(
id int (11) primary key auto_increment,
class char(255),score char(255),
student_id int(11)
)charset utf8;
1
2
3
4
5
6
7
8
9
10
11
12
13
（2）创建外键
ALTER TABLE student_score
ADD CONSTRAINT s_id FOREIGN KEY (student_id) REFERENCES student (id);
1
2
（3）查看表结构
SHOW CREATE TABLE student;
SHOW CREATE TABLE student_score;
1
2
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(255) DEFAULT NULL,
`sex` char(255) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
1
2
3
4
5
6
7
CREATE TABLE `student_score` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`class` char(255) DEFAULT NULL,
`score` char(255) DEFAULT NULL,
`student_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `s_id` (`student_id`),
CONSTRAINT `s_id` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
1
2
3
4
5
6
7
8
9
三、验证外键作用
1 先向主表中添加数据
再向从表中添加数据（从表中的外键已在主表中存在），正常运行。
#向 student 表中添加数据
INSERT INTO student (NAME, sex, age)
VALUES
('小明', '男', '20');
#向 student_score 表中添加数据
INSERT INTO student_score (class, score, student_id)
VALUES
('语文', '100', 1),
('数学', '99', 1),
('英语', '98', 1);
1
2
3
4
5
6
7
8
9
10
11
数据插入正常，无报错、警告信息。
2 触发限制使用默认值 RESTRICT 的情况下
（1）从表插入新行，外键值不在主表中，被阻止
INSERT INTO student_score (class, score, student_id)
VALUES
('语文', '100', 2);
1
2
3
ERROR 1452 (23000) : Cannot ADD
OR UPDATE a child ROW : a FOREIGN KEY CONSTRAINT fails (
`tts`.`student_score`,
CONSTRAINT `s_id` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`)
)
1
2
3
4
5
（2）从表修改外键值，新值不是主表的主键值，阻止修改
UPDATE student_score
SET student_id = 2
WHERE
student_id = 1;
1
2
3
4
ERROR 1452 (23000) : Cannot ADD
OR UPDATE a child ROW : a FOREIGN KEY CONSTRAINT fails (
`tts`.`student_score`,
CONSTRAINT `s_id` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`)
)
1
2
3
4
5
（3）主表删除行，其主键值在从表里存在便阻止删除(要想删除，必须先删除从表的相关行)
DELETE FROM student WHERE id = 1;
1
ERROR 1451 (23000) : Cannot DELETE
OR UPDATE a parent ROW : a FOREIGN KEY CONSTRAINT fails (
`tts`.`student_score`,
CONSTRAINT `s_id` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`)
)
1
2
3
4
5
（4）主表修改主键值，旧值在从表里存在便阻止修改(要想修改，必须先删除从表的相关行)
UPDATE student SET id = 2 WHERE id = 1;
1
ERROR 1451 (23000) : Cannot DELETE
OR UPDATE a parent ROW : a FOREIGN KEY CONSTRAINT fails (
`tts`.`student_score`,
CONSTRAINT `s_id` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`)
)
1
2
3
4
5
3 更改事件触发限制为 CASCADE
#删除旧的外键
ALTER TABLE student_score DROP FOREIGN KEY s_id;
#添加新的外键，修改事件触发限制为 CASCADE
ALTER TABLE student_score
ADD CONSTRAINT s_id FOREIGN KEY (student_id) REFERENCES student (id)
ON DELETE CASCADE ON UPDATE CASCADE;
1
2
3
4
5
6
7
（1）查看表结构
CREATE TABLE `student_score` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`class` char(255) DEFAULT NULL,
`score` char(255) DEFAULT NULL,
`student_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `s_id` (`student_id`),
CONSTRAINT `s_id` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
1
2
3
4
5
6
7
8
9
（2）查看此时两表中的数据
（3）此时，当主表修改主键值，从表中相关行的外键值将一起修改
UPDATE student SET id = 2 WHERE id = 1;
1
（4）如果主表删除行，从表中的相关行将一起被删除
DELETE FROM student WHERE id = 2;
1
4 结论
事件触发限制条件的不同，会造成两张表中的操作限制不同，其他几个限制条件相对好理解，大家可以自己进行尝试，体会其中的区别。
四、删除外键约束
ALTER TABLE 表名 DROP FOREIGN KEY 外键名;
ALTER TABLE student_score DROP FOREIGN KEY s_id;
————————————————
版权声明：本文为CSDN博主「知其黑、受其白」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weiguang102/article/details/126409406