MySQL中外鍵的定義、作用、添加和刪除

1 簡介

在實際開發的項目中，一個健壯數據庫中的數據一定有很好的參照完整性。例如學生檔案和成績單兩張表，如果成績單中有張三的成績，學生檔案中張三的檔案卻被刪除了，這樣就會產生垃圾數據或者錯誤數據。為了保證數據的完整性，將兩張表之間的數據建立關系，因此就需要在成績表中添加外鍵約束。

2?? ?外鍵的定義
外鍵是指引用另外一個表中的一列或多列數據，被引用的列應該具有主鍵約束或者唯一性約束。外鍵用來建立和加強兩個表數據之間的連接。

3?? ?材料準備
mysql> create table grade(
? ? -> id int(4) not null primary key,
? ? -> name varchar(36)
? ? -> );
Query OK, 0 rows affected (1.14 sec)
?
上表為班級表。

mysql> drop table student;
Query OK, 0 rows affected (0.41 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| grade ? ? ? ? ?|
+----------------+
1 row in set (0.00 sec)

mysql> create table student (
? ? -> sid int(4) not null primary key,
? ? -> sname varchar(36),
? ? -> gid int(4) not null
? ? -> );
Query OK, 0 rows affected (0.44 sec)
?
上述的代碼片段則是首先刪除在本數據庫中的student表，然后創建student表。
首先建立兩張表，student和grade，學生表中的gid是學生所在的班級id，是引入了班級表grade中的主鍵id。那么gid就可以作為表student表的外鍵。被引用的表，即表grade是主表，引用外鍵的表，即student，是從表。兩個表是主從關系。表student用gid可以連接表grade中的信息，從而建立了兩個表中的連接。
可以這么理解，外鍵即依賴關系，可以明確的聲明表和表之間的關系額字段的參照關系，這種就叫做表和表之間聲明了一個外鍵。

注意：引入外鍵之后，外鍵列只能插入參照列存在的值，參照列被參照的值不能被刪除，這就保證了數據的參照完整性。

4?? ?外鍵的添加和刪除
4.1?? ?添加外鍵
為表添加外鍵約束的語法格式如下：

alert table 表名 add constraint FK_ID foreign key(外鍵字段名) references 外表表名(主鍵字段名)
?
其中FK_ID為外鍵的名稱，是隨意的。
為表student添加外鍵約束，具體語句如下：

mysql> alter table student add constraint FK_ID foreign key gid references grade id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'references grade id' at line 1
mysql> alter table student add constraint FK_ID foreign key (gid) references grade (id);
Query OK, 0 rows affected (1.27 sec)
?
語句執行成功后，使用DESC語句來查看學生表和班級表，查詢結果如下：

mysql> desc grade;
+-------+-------------+------+-----+---------+-------+
| Field | Type ? ? ? ?| Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id ? ?| int(4) ? ? ?| NO ? | PRI | NULL ? ?| ? ? ? |
| name ?| varchar(36) | YES ?| ? ? | NULL ? ?| ? ? ? |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.06 sec)

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type ? ? ? ?| Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sid ? | int(4) ? ? ?| NO ? | PRI | NULL ? ?| ? ? ? |
| sname | varchar(36) | YES ?| ? ? | NULL ? ?| ? ? ? |
| gid ? | int(4) ? ? ?| NO ? | MUL | NULL ? ?| ? ? ? |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
?
可以從上述的輸出中看出表grade中id為主鍵，student表中gid為外鍵，但是結果不能明確的看出兩個表之間的關系。在MySQL中可以用show create table來查看表的詳細結構，具體語句如下：

mysql> show create table student;
+---------+------------------------------------------------------------
| Table ? | Create Table
+---------+------------------------------------------------------------
| student | CREATE TABLE `student` (
? `sid` int(4) NOT NULL,
? `sname` varchar(36) DEFAULT NULL,
? `gid` int(4) NOT NULL,
? PRIMARY KEY (`sid`),
? KEY `FK_ID` (`gid`),
? CONSTRAINT `FK_ID` FOREIGN KEY (`gid`) REFERENCES `grade` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+------------------------------------------------------------
1 row in set (0.06 sec)
?
4.2?? ?驗證外鍵的作用
mysql> insert into student (sid, sname, gid) values(1000, 'wusong', 123);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`student`, CONSTRAINT `FK_ID` FOREIGN KEY (`gid`) REFERENCES `grade` (`id`))
1
2
上述錯誤表示，若要向student中插入學生數據，則必須插入已經在grade中存在的gid值，實際意義就是要把新來的學生放入某個班級，則班級必須先存在。

mysql> insert grade values(10, '一班');
Query OK, 1 row affected (0.16 sec)

mysql> insert grade values(11, '二班');
Query OK, 1 r，ow affected (0.12 sec)

mysql> insert grade values('', 1);
ERROR 1366 (HY000): Incorrect integer value: '' for column 'id' at row 1
mysql> insert grade values(12, 1); ?//可見整數1可以自動轉換為varchar1
Query OK, 1 row affected (0.09 sec)
mysql> select * from grade;
+----+--------+
| id | name ? |
+----+--------+
| 10 | 一班 ? |
| 11 | 二班 ? |
| 12 | 1 ? ? ?|
+----+--------+
3 rows in set (0.00 sec)
?
通過插入語句，已經向grade中插入了三個班級。向班級1中插入數據和班級2中插入。學生記錄維持在表student中。

mysql> insert into student(sid, sname, gid) values(1000, '周華健', 10);
Query OK, 1 row affected (0.11 sec)

mysql> insert into student(sid, sname, gid) values(1001, '周芷若', 10);
Query OK, 1 row affected (0.17 sec)

mysql> insert into student(sid, sname, gid) values(1002, '周杰倫', 10);
Query OK, 1 row affected (0.19 sec)
以同樣的方式向班級2中插入兩條數據
mysql> insert into student(sid, sname, gid) values(10003, '趙云', 11), (10004, '趙子龍', 11);
Query OK, 2 rows affected (0.12 sec)
Records: 2 ?Duplicates: 0 ?Warnings: 0
?
可以插入gid不存在的班級嗎？

mysql> insert into student(sid, sname, gid) values(10003, '趙云', 0);
ERROR 1062 (23000): Duplicate entry '10003' for key 'PRIMARY'
mysql> insert into student(sid, sname, gid) values(10004, '趙云', 0);
ERROR 1062 (23000): Duplicate entry '10004' for key 'PRIMARY'
mysql> insert into student(sid, sname, gid) values(10005, '趙云', 0);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`student`, CONSTRAINT `FK_ID` FOREIGN KEY (`gid`) REFERENCES `grade` (`id`))
?
上述的代碼片段中演示了插入數據時外鍵所起的作用，那么刪除時外鍵依然會外參照完整性提供保護。

mysql> select * from grade;
+----+--------+
| id | name ? |
+----+--------+
| 10 | 一班 ? |
| 11 | 二班 ? |
| 12 | 1 ? ? ?|
+----+--------+
3 rows in set (0.00 sec)

mysql> select * from student;
+-------+-----------+-----+
| sid ? | sname ? ? | gid |
+-------+-----------+-----+
| ?1000 | 周華健 ? ?| ?10 |
| ?1001 | 周芷若 ? ?| ?10 |
| ?1002 | 周杰倫 ? ?| ?10 |
| 10003 | 趙云 ? ? ?| ?11 |
| 10004 | 趙子龍 ? ?| ?11 |
+-------+-----------+-----+
5 rows in set (0.00 sec)
?
如果此時要直接刪除grade中的班級1會發生什么？

mysql> delete from grade where id=10;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`student`, CONSTRAINT `FK_ID` FOREIGN KEY (`gid`) REFERENCES `grade` (`id`))
mysql> delete from grade where id=12;
Query OK, 1 row affected (0.16 sec)
?
上述代碼片段id為10在刪除記錄時彈出了Error，而刪除id為12的記錄時可以成功執行，是因為在student表中有依賴id=10的記錄存在，因此無法刪除。

4.3?? ?外鍵關聯表聯合刪除
建立外鍵是為了保證數據的完整和統一性，但如果主表中的數據被刪除或修改，從表中的數據該怎么辦？很明顯應該刪除，否則數據庫中會存在很多無意義的垃圾數據。為此，MySQL可以在建立外鍵時添加ON DELETE或ON UPDATE子句來告訴數據庫，怎樣避免垃圾數據的產生。

alter table 表名 add constraint FK_ID foreign key (外鍵字段名) references 外表表名 (主鍵字段名)
[on delete {cascade | set null | no action| restrict}]
[on update {cascade | set null | no action| restrict}]
?
其中restrict是默認操作，表示拒絕主表刪除或修改外鍵關聯列，這是最安全的設置。
而cascade表示刪除包含與已刪除鍵值有參考關系的所有記錄。

4.4?? ?刪除外鍵約束
在實際開發中，根據業務邏輯的需求，需要解除兩個表之間的關聯關系時，就需要刪除外鍵約束。刪除外鍵約束的語法如下：

alter table 表名 drop foreign key 外鍵名;
1
如果解除student表的外鍵約束，具體語句如下：

mysql> show create table student;
+---------+--------------------------------------------------------------
| Table ? | Create Table
+---------+--------------------------------------------------------------
| student | CREATE TABLE `student` (
? `sid` int(4) NOT NULL,
? `sname` varchar(36) DEFAULT NULL,
? `gid` int(4) NOT NULL,
? PRIMARY KEY (`sid`),
? KEY `FK_ID` (`gid`),
? CONSTRAINT `FK_ID` FOREIGN KEY (`gid`) REFERENCES `grade` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+--------------------------------------------------------------
1 row in set (0.00 sec)

mysql> alter table student drop foreign key FK_ID;
Query OK, 0 rows affected (0.19 sec)
Records: 0 ?Duplicates: 0 ?Warnings: 0
?
此時表grade和student的參照關系便被移除了，

mysql> insert into student (sid, sname, gid) values (1003, '劉亦菲', 5);
Query OK, 1 row affected (0.13 sec)

mysql> delete from student where id=10;
ERROR 1054 (42S22): Unknown column 'id' in 'where clause'
mysql> delete from grade where id=10; //此時student表中存在gid為10的記錄
Query OK, 1 row affected (0.13 sec)

mysql> show create table student;
+---------+------------------------------------------------------
| Table ? | Create Table
+---------+------------------------------------------------------
| student | CREATE TABLE `student` (
? `sid` int(4) NOT NULL,
? `sname` varchar(36) DEFAULT NULL,
? `gid` int(4) NOT NULL,
? PRIMARY KEY (`sid`),
? KEY `FK_ID` (`gid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+------------------------------------------------------
1 row in set (0.00 sec)

mysql> insert into student (sid, sname, grade^C
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type ? ? ? ?| Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sid ? | int(4) ? ? ?| NO ? | PRI | NULL ? ?| ? ? ? |
| sname | varchar(36) | YES ?| ? ? | NULL ? ?| ? ? ? |
| gid ? | int(4) ? ? ?| NO ? | MUL | NULL ? ?| ? ? ? |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
?
由于參照關系被移除了，則插入student表或者刪除表grade記錄則不再存在參照依賴關系了，由上述的代碼片段可以看到結果。

5?? ?總結
在多表查詢中，外鍵是參照關系的提現，而明確的理解外鍵關系的含義，了解外鍵關系的添加和刪除對于mysql數據庫表之間的關系，對增強數據庫表設計的理解，是程序員增強數據結構存儲的必備知識儲備。
?————————————————?
?