sql_外键

一、外键的定义

1. 外键是一种索引，是通过一张表中的一列指向另一张表的主键，使得这两张表产生关联

2. 是某个表中的一列，它包含在另一个表的主键中

3. 一张表中可以有一个外键也可以有多个外键

二、外键的作用

减少单张表的冗余信息

三、使用外键后想要删除/更新主表的相关列会报错，因为从表正在使用。使用外键后若想向从表中插入新列，但是外键值主表中没有，会报错，更新从表的外键值，但是值不在从表中会报错。

 

四、如何创建外键

法1：创建表的时候添加外键

CONSTRAINT 约束名 FOREIGN KEY(字段名) REFERENCES 主表（被引用列）
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

CASCADE：父表delete、update的时候，子表会delete、update掉关联记录；
SET NULL：父表delete、update的时候，子表会将关联记录的外键字段所在列设为null，所以注意在设计子表时外键不能设为not null；
RESTRICT：如果想要删除父表的记录时，而在子表中有关联该父表的记录，则不允许删除父表中的记录；
NO ACTION：同 RESTRICT，也是首先先检查外键；

其中跟随外键改动是：删除主表中，外键行时，从表相关行也删除。更新主表中外键相关行时，从表的使用行也改变

若，事触发限制为默认值，限制外表中外键的改动，则在添加外键之后对表中数据的增删改查都需要注意

例：

CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
sex VARCHAR(255),
age INT);

CREATE TABLE IF NOT EXISTS student_score(
id INT PRIMARY KEY AUTO_INCREMENT,
class VARCHAR(20),
score INT,
student_id INT,
CONSTRAINT fk_s_id FOREIGN KEY(student_id) REFERENCES student(id))

法2：修改表时添加外键约束

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.向从表中插入新行，但是外键值不在主表中，会报错

#向从表中插入新行，但是外键值不在主表中，会报错
INSERT INTO student_score(`class`,`student_id`,`score`) VALUES ('语文',2,100)

 2.

#更新从表外键值，新值不在主表中，会报错
UPDATE student_score SET student_id=2
WHERE student_id=1

3.

#主表想要删除行，但是主键值在从表里存在，会报错（必须先删除从表使用行，才能再删主表的相关行）--或者采用级联删除

DELETE FROM student
WHERE id=1;


#级联删除 on delete cascade
 
ALTER TABLE student_score DROP FOREIGN KEY fk_s_id;

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

4.

#主表修改主键值，但是旧值在从表中使用了，会报错，必须先删除从表的使用行，才能再修改--或者采用级联更新
#级联更新
on update cascadde


UPDATE student SET id = 2 WHERE id = 1;


ALTER TABLE student_score DROP FOREIGN KEY fk_s_id;

ALTER TABLE student_score ADD CONSTRAINT fk_s_id FOREIGN KEY(student_id)
REFERENCES student(id) ON update CASCADE