MySQL的外键与参照完整性:Part1_MySQL

转自：http://sofee.cn/blog/2006/08/07/26/

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. 总结
——-
引入外键的缺点是会使速度和性能下降，当然外键所带来的优点还有很多，本文仅讨论如何定义、删除外键。至于外键的实际应用将会在以后的文章中介绍