MySQL的外鍵與參照完整性:Part1_MySQL

轉(zhuǎn)自：http://sofee.cn/blog/2006/08/07/26/

1. 什么是參照完整性?
——————–
參照完整性(完整性約束)是數(shù)據(jù)庫設(shè)計中的一個重要概念，當(dāng)數(shù)據(jù)庫中的一個表與一個或多個表進(jìn)行關(guān)聯(lián)時都會涉及到參照完整性。比如下面這個例子：
文章分類表 - 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的這條記錄不應(yīng)該存在在articles表中，這樣會很容易造成數(shù)據(jù)錯亂。

相反，外鍵關(guān)系(Foreign Key relationships)討論的是父表(categories)與子表(articles)的關(guān)系，通過引入外鍵(Foreign Key)這個概念來保證參照完整性(Referential integrity)，將使會數(shù)據(jù)庫變的非常簡單。比如，要要做到刪除categories表中category_id=4記錄的同時刪除 articles 表中category_id=4的所有記錄，如果沒有引入外鍵的話，我們就必須執(zhí)行2條SQL語句才行；如果有外鍵的話，可以很容易的用一條SQL語句就可以達(dá)到要求。

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) 相關(guān)聯(lián)的表都必須采用InnoDB引擎。

3) 相關(guān)聯(lián)的字段都必須建立所以。
MySQL v4.0版本以后，定義外鍵時會自動建立所以，所以在 v4.0 版本以前（含v4.0版本）必須手工定義索引。

4) 相關(guān)聯(lián)的字段必須采用類似的數(shù)據(jù)類型，或者說可轉(zhuǎn)換的數(shù)據(jù)類型，當(dāng)然相同類型是最好不過了。
比如父表的字段是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, ‘a(chǎn)dmin’);
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表，F(xiàn)OREIGN KEY子句會被忽略掉。
如果遇到如下錯誤：
ERROR 1005: Can’t create table ‘./test/articles.frm’ (errno: 150)
請仔細(xì)檢查以下定義語句，常見的錯誤一般都是表類型不是INNODB、相關(guān)聯(lián)的字段寫錯了、缺少索引等等。

至此categories.category_id和articles.category_id、members.member_id和articles.member_id已經(jīng)建立外鍵關(guān)系，只有 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`))

可見上面兩條語句都會出現(xiàn)錯誤，因為在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. 刪除外鍵定義
—————
不知道大家有沒有發(fā)現(xiàn)，在前面定義外鍵的時候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會自己創(chuàng)建一個，可以通過以下命令查看：

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，因為就可以執(zhí)行以下命令刪除外鍵定義：

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. 總結(jié)
——-
引入外鍵的缺點是會使速度和性能下降，當(dāng)然外鍵所帶來的優(yōu)點還有很多，本文僅討論如何定義、刪除外鍵。至于外鍵的實際應(yīng)用將會在以后的文章中介紹

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識，若有侵權(quán)等問題請及時與本網(wǎng)聯(lián)系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com