1. 什麼是參照完整性?
——————–
參照完整性(完整性約束)是數據庫設計中的一個重要概念，當數據庫中的一個表與一個或多個表進行關聯時都會涉及到參照完整性。比如下面這個例子：
文章分類表 - categorIEs
category_id name
1 SQL Server
2 Oracle
3 PostgreSQL
5 SQLite
文章表 - articles
article_id category_id title
1 1 aa
2 2 bb
3 4 cc
可見以上兩個表之間是通過category_id，其中categorIEs表有4條記錄，articles表有3條記錄。
然而可能因為某種原因我們刪掉了categorIEs 表中category_id=4的記錄，而articles表卻還是有一條category_id=4的記錄，很明顯，category_id=4的這條記錄不應該存在在articles表中，這樣會很容易造成數據錯亂。
相反，外鍵關系(Foreign Key relationships)討論的是父表(categorIEs)與子表(articles)的關系，通過引入外鍵(Foreign Key)這個概念來保證參照完整性(Referential integrity)，將使會數據庫變的非常簡單。比如，要要做到刪除categorIEs表中category_id=4記錄的同時刪除 articles 表中category_id=4的所有記錄，如果沒有引入外鍵的話，我們就必須執行2條SQL語句才行；如果有外鍵的話，可以很容易的用一條SQL語句就可以達到要求。
2. 使用外鍵的條件
—————–
MySQL只在v3.23.34版本以後才引入外鍵的，所以在這之前的版本就別想了:)，除此之外，還必須具備以下幾個條件：
1) 在my.cnf配置文件中打開InnoDB引擎支持。
# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /var/db/MySQL/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/db/MySQL/
innodb_log_arch_dir = /var/db/MySQL/
2) 相關聯的表都必須采用InnoDB引擎。
3) 相關聯的字段都必須建立所以。
MySQL v4.0版本以後，定義外鍵時會自動建立所以，所以在 v4.0 版本以前（含v4.0版本）必須手工定義索引。
4) 相關聯的字段必須采用類似的數據類型，或者說可轉換的數據類型，當然相同類型是最好不過了。
比如父表的字段是TINYINT類型，則子表只能采用TINYINT、SMALLINT、INT、BIGINT等幾種類型。
3. 外鍵語法參考
—————
可以通過 CREATE TABLE 或者 ALTER TABLE 來定義外鍵。
CREATE TABLE 語法：
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,…)]
create_definition:
column_definition
| [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,…) [reference_definition]
column_definition:
col_name type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT ’string’] [reference_definition]
index_col_name:
col_name [(length)] [ASC | DESC]
reference_definition:
REFERENCES tbl_name [(index_col_name,…)]
[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION
ALTER TABLE 語法：
ALTER [IGNORE] TABLE tbl_name
alter_specification [, alter_specification] …
alter_specification:
| ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,…)
| ADD [CONSTRAINT [symbol]] UNIQUE [INDEX] [index_name] [index_type] (index_col_name,…)
| ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,…) [reference_definition]
| DROP FOREIGN KEY fk_symbol
4. 定義外鍵
———–
MySQL> CREATE TABLE categorIEs (
-> category_id tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
-> name varchar(30) NOT NULL,
-> PRIMARY KEY(category_id)
-> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.36 sec)
MySQL> INSERT INTO categorIEs VALUES (1, ‘SQL Server’), (2, ‘Oracle’), (3, ‘PostgreSQL’), (4, ‘MySQL’), (5, ‘SQLite’);
Query OK, 5 rows affected (0.48 sec)
Records: 5 Duplicates: 0 Warnings: 0
MySQL> CREATE TABLE members (
-> member_id INT(11) UNSIGNED NOT NULL,
-> name VARCHAR(20) NOT NULL,
-> PRIMARY KEY(member_id)
-> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.55 sec)
MySQL> INSERT INTO members VALUES (1, ‘test’), (2, ‘admin’);
Query OK, 2 rows affected (0.44 sec)
Records: 2 Duplicates: 0 Warnings: 0
MySQL> CREATE TABLE articles (
-> article_id INT(11) unsigned NOT NULL AUTO_INCREMENT,
-> title varchar(255) NOT NULL,
-> category_id tinyint(3) unsigned NOT NULL,
-> member_id int(11) unsigned NOT NULL,
-> INDEX (category_id),
-> FOREIGN KEY (category_id) REFERENCES categorIEs (category_id),
-> CONSTRAINT fk_member FOREIGN KEY (member_id) REFERENCES members (member_id),
-> PRIMARY KEY(article_id)
-> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.63 sec)
注意：對於非InnoDB表，FOREIGN KEY子句會被忽略掉。
如果遇到如下錯誤：
ERROR 1005: Can’t create table ‘./test/articles.frm’ (errno: 150)
請仔細檢查以下定義語句，常見的錯誤一般都是表類型不是INNODB、相關聯的字段寫錯了、缺少索引等等。
至此categories.category_id和articles.category_id、members.member_id和 articles.member_id已經建立外鍵關系，只有 articles.category_id 的值存在與 categorIEs.category_id 表中並且articles.member_id的值存在與members.member_id表中才會允許被插入或修改。例如：
MySQL> INSERT INTO articles (category_id, member_id, title) VALUES (6, 1, ‘foo’);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test/articles`, CONSTRAINT `articles_ibfk_1` FOREIGN KEY (`category_id`)REFERENCES `categorIEs` (`id`))
MySQL> INSERT INTO articles (category_id, member_id, title) VALUES (3, 3, ‘foo’);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test/articles`, CONSTRAINT `fk_member` FOREIGN KEY (`member_id`) REFERENCES `members` (`member_id`))
可見上面兩條語句都會出現錯誤，因為在categorIEs表中並沒有category_id=6、members表中也沒有member_id=3的記錄，所以不能插入。而下面這條SQL語句就可以。
MySQL> INSERT INTO articles (category_id, member_id, title) VALUES (3, 2, ‘bar’);
Query OK, 1 row affected (0.03 sec)
5. 刪除外鍵定義
—————
不知道大家有沒有發現，在前面定義外鍵的時候articles.member_id外鍵比articles.category_id子句多了一個CONSTRAINT fk_member ？
這個fk_member就是用來刪除外鍵定義用的，如下所示：
MySQL> ALTER TABLE articles DROP FOREIGN KEY fk_member;
Query OK, 1 row affected (0.25 sec)
Records: 1 Duplicates: 0 Warnings: 0
這樣articles.member_id外鍵定義就被刪除了，但是如果定義時沒有指定CONSTRAINT fk_symbol （即外鍵符號）時該怎麼刪除呢？別急，沒有指定時，MySQL會自己創建一個，可以通過以下命令查看：
MySQL> SHOW CREATE TABLE articles;
+———-+————————————+
| Table | Create Table |
+———-+————————————+
| articles | CREATE TABLE `articles` (
`article_id` int(11) unsigned NOT NULL auto_increment,
`category_id` tinyint(3) unsigned NOT NULL,
`member_id` int(11) unsigned NOT NULL,
`title` varchar(255) NOT NULL,
PRIMARY KEY (`article_id`),
KEY `category_id` (`category_id`),
KEY `member_id` (`member_id`),
CONSTRAINT `articles_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `categorIEs` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+———-+————————————+
1 row in set (0.01 sec)
可以看出articles.category_id的外鍵符號為articles_ibfk_1，因為就可以執行以下命令刪除外鍵定義：
MySQL> ALTER TABLE articles DROP FOREIGN KEY articles_ibfk_1;
Query OK, 1 row affected (0.66 sec)
Records: 1 Duplicates: 0 Warnings: 0
6. 總結
——-
引入外鍵的缺點是會使速度和性能下降，當然外鍵所帶來的優點還有很多，本文僅討論如何定義、刪除外鍵。至於外鍵的實際應用將會在以後的文章中介紹。
