MySQL——外鍵

標籤：作用   定義   null   語句   實體   reference   從表   測試   一個   

概念

關鍵字：foreign key，也叫做外鍵約束！

如果一個實體A的某個欄位，剛好指向另一個實體B的主鍵，那麼實體A的這個欄位就叫做外鍵；

所以，簡單來說，外鍵就是本表的某個欄位指向外表的主鍵！

 

 

上面的，學生表中的班級欄位其實就是一個外鍵！

 其中，被指向的實體集（班級表），叫做主實體集（主表），也叫做父實體集（父表），負責指向的實體集（學生表），叫做從實體集（從表），也叫做子實體集（子表）

作用

外鍵的意義就是用來約束關係內的實體！

為什麼需要有外鍵約束，因為需要在插入記錄的時候，保證其符合現實中的正常的邏輯關係：

 假如上面的有一個學生的班級欄位裡面出現了11班，就不符號邏輯，因為班級表中根本就沒有11班！

所以，外鍵的約束主要體現在以下的兩個方面：

1，  增加子表記錄的時候，需要判斷是否有與之對應的父表記錄！

2，  當刪除或更改父表記錄的時候，子表應該如何處理相關的記錄！

定義

應該在從表的某個欄位上增加一個外鍵屬性用於執行主表的主鍵！

 1 -- 定義父表 2 create table web_class( 3     class_id int unsigned primary key auto_increment, 4     class_teacher varchar(20) not null 5 ); 6  7 -- 定義子表 8 create table web_student( 9     stu_id int unsigned primary key auto_increment,10     stu_name varchar(20) not null,11     stu_home varchar(100),12     stu_class_id int unsigned,13     -- 定義外鍵14     foreign key(stu_class_id) references web_class(class_id)15 );

設定級聯操作

級聯操作(關聯動作)：操作一張表，影響另一張表
1.主表更新
文法形式: on update[級聯操作]
2.主表刪除
文法形式: on delete[級聯操作]

上面的級聯操作常見的都有三種形式：
cascade: 同步操作，串聯操作，也就是當主表的欄位更新或者刪除的時候
從表的外鍵也進行刪除或者更新

set null: 設定為空白，也就是當主表的欄位更新或者更新或者刪除的時候，從表的外鍵欄位就設定為null
當然,前提是從表的外鍵欄位沒有非空約束

restrict: 就是拒絕主表更新或者刪除

所以，常見的級聯操作一共有9種，

-- on update cascade on delete cascade-- on update cascade on delete set null-- on update cascade on delete restrict-- on update set null on delete cascade-- on update set null on delete set null-- on update set null on delete restrict--on update restrict on delete cascade--on update restrict on delete set null--on update restrict on delete restrict

 

所以完整的定義外鍵方式文法：

1 -- foreign key(從表的外鍵欄位) references 主表名(主表的主鍵)2 on update cascade|set null|restrict on delete cascade|set null|restrict

 

 

刪除外鍵：
文法：

1 /*2 刪除外鍵3 alter table 表名 drop foreign key 外鍵名;4 這裡的外鍵名不是外鍵的欄位名，而是系統在定義外鍵的時候自動分配的一個名字5 可以通過show create table 表名\G;查看；6 */7 alter table web_student drop foreign key `web_student_ibfk_1`;

 

 

-- 增加外鍵

/*alter table 表名 add foreign key(從表的外鍵欄位) reference 主表名(主表的住主鍵) 級聯操作;alter table 表名 add foreign key(從表的外鍵欄位) references 主表名(主表的主鍵)on update cascade|set null|restrict on delete cascade|set null|restrict*/alter table web_student add foreign key(stu_class_id) references web_calss(class_id)on update cascade on delete set null;

 

測試

1 -- 先在主表上更新2 update web_class set class_id = 8 where class_id = 7;

1 --查詢子表2 select * from web_student;

1 -- 先在主表上刪除2 delete web_class where class_id = 8;

1 --查詢子表2 select * from web_student;

 

測試其他的級聯操作

注意：
1.在某些大型的項目中，可能只會用到外鍵的設計思想，但是往往不會真正的從文法上
進行外鍵操作，因為外鍵的級聯操作可能會見底mysql執行速度！而往往使用
兩條sql語句去替代外鍵的級聯操作(分別操作的sql語句)

2.外鍵的約束，只有在InnoDB儲存引擎上儲存才能執行

MySQL——外鍵