補12.關于mysql的外鍵約束

一、什么是mysql中的外鍵（froeign key）

假如說有兩張表，其中一張表的某個字段指向了另一張表的主鍵，這就可以稱之為外鍵(froeign key)。

在子表中增加一條記錄時，需要確定是否有與父表相對應的記錄。

如果父表沒有對應的記錄，那么子表（從表）無法插入這條數據。

下面是一個關于外鍵的示例：

1.首先創建一個主表，這個主表存放了班級信息。

create table class ( id TINYINT PRIMARY KEY auto_increment, class_name varchar(20)) engine=innodb;

2.在class表中插入五條記錄，代表有5個班級。

insert into class (id,class_name)value(1,"class1"), (2,"class2"), (3,"class3"), (4,"class4"), (5,"class5");

3.創建一個子表，這個子表存放了學生和班級的對應關系，這些學生屬于哪個班級？

create table student( id int primary key auto_increment, name varchar(20), class_id TINYINT) engine=innodb;

4.插入6條記錄，代表了6個學生。

insert into student(id,name,class_id) value (1,"stu1",2),(2,"stu2",2),(3,"stu3",1),(4,"stu4",3),(5,"stu5",1),(6,"stu6",4);

現在的規則是，一個班級可以對應多個學生，但是一個學生只能屬于一個班級。

5.增加外鍵約束。

接下來，為student表增加一個外鍵約束。

alter table student add constraint cls_stu foreign key(class_id) references class(id);

#約束字段以class表的id字段為主。

6.測試外鍵約束是否生效。

剛剛我們創建的class班級表中一共有5個記錄，也就是五個班級。（分別是class1~class5）

現在學生表中，新增一個學生，這個學生屬于一個class表中不存在的班級，班級6，看看會是什么效果：

mysql> insert into student(id,name,class_id)value(7,"stu7",6);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db_1`.`student`, CONSTRAINT `cls_stu` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))

無法添加這個記錄，這說明了，student表的class_id字段，已經受class表的id字段的約束，當class表的id字段沒有這個id時，student表無法插入這條記錄。

7.刪除一個表的外鍵約束。

ALTER TABLE student DROP FOREIGN KEY 【外鍵名稱】

例：

alter table student drop foreign key cls_stu;

8.innodb引擎支持的一些關于外鍵的操作。

#當一個表被設置了外鍵約束，如果在父表中找不到候選鍵,則不允許在子表上進行insert/update操作。

# 注意！！！在父表上進行update/delete以更新或刪除在子表中有一條或多條對應匹配行的候選鍵時,父表的行為取決于：在定義子表的外鍵時指定的on update或on delete子句！！！

#下面是innodb引擎所支持的常見的四種方式：

8.1cascade方式 在父表上update/delete記錄時，同步update/delete掉子表的匹配記錄

外鍵的級聯刪除：如果父表中的記錄被刪除，則子表中對應的記錄自動被刪除

alter table student add constraint cls_stu foreign key(class_id) references class(id) on delete cascade;

#級聯刪除或更改，當父表外鍵位置做出修改，或者刪除，子表更改或刪除完全取決于 on delete或者on update，當然，這兩個關鍵字是可以同時出現的！！！

下面是一個級聯刪除關鍵字的示例：

alter table student add constraint cls_stu foreign key(class_id) references class(id) on delete cascade;

8.2 set null方式 在父表上update/delete記錄時，將子表上匹配記錄的列設為null。

例：

alter table student add constraint cls_stu foreign key(class_id) references class(id) on delete set null;

8.3 Restrict方式 :拒絕對父表進行刪除更新操作(不常用)。

8.4 No action方式 在mysql中同Restrict,如果子表中有匹配的記錄,則不允許對父表對應候選鍵進行update/delete操作（了解）

9.關于外鍵的一些補充：

設置級聯操作：

在主表數據發生改變時，與之關聯的從表數據應該如何處理

    使用關鍵字：

     on update

     on delete

    來標識

允許的級聯動作：

cascade關聯操作，如果主表被更新或刪除，從表也會執行相應的操作

set null,表示從表數據不指向主表任何記錄

restrict：拒絕主表的相關操作

alter table t_student add foreign key (class_id) references t_class (class_id)

on delete set null; # 在刪除外鍵時，將從表的外鍵值設置為null

修改外鍵：

先刪除這個表的外鍵，然后再增加

alter table tb_name drop froeign key 外鍵名稱

外鍵名稱在建立外鍵時可以自定義名稱，如果不自定義，會按照mysql自動生成一個名稱

show create table tb_name;