MySQL的外键和参照完整性

一、参照完整性(Referential integrity)和外键(foreign key)

参照完整性(Referential integrity)是用于约定两个关系之间的联系，理论上规定：若M是关系S中的一属性组，且M是另一关系Z的主关键字，则称M为关系S对应关系Z的外关键字。若M是关系S的外关键字，则S中每一个元组在M上的值必须是空值或是对应关系Z中某个元组的主关键字值。参照完整性是数据库设计中一个重要的概念。在系统不同的表中，当数据库所有参照合法或非合法关联时都会涉及到参照完整性。当参照完整性存在时，任何与不存在的记录产生关联将变得无效，由此可防止用户出现各种错误，从而提供更为准确和实用的数据库。

外键(foreign key):如果表A的主关键字是表B中的字段，则该字段称为表B的外键，表A称为主表，表B称为从表。外键是用来实现参照完整性的，不同的外键约束方式将可以使两张表紧密的结合起来，特别是修改或者删除的级联操作将使得日常的维护工作更加轻松。

二、MySQL中的外键使用条件

MySQL以往并不支持外键，原因是这种支持会降低速度和性能。

在MySQL 3.23.44版本后，InnoDB引擎类型的表支持了外键约束，但是默认引擎MyISAM目前还不支持外键，因此使用时需要注意建立数据库表时选择InnoDB作为数据库引擎。
外键的使用条件：

1.两个表必须是InnoDB表，MyISAM表暂时不支持外键（据说以后的版本有可能支持，目前我使用的是5.1.36还没有支持）；
2.外键列必须建立了索引，MySQL 4.1.2以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显示建立；
3.外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如int和tinyint可以，而int和char则不可以；

三、外键的语法

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
该语法可以在 CREATE TABLE 和 ALTER TABLE 时使用，如果不指定CONSTRAINT symbol，MYSQL会自动生成一个名字。
ON DELETE、ON UPDATE表示事件触发限制，可设参数：
RESTRICT（限制外表中的外键改动）
CASCADE（跟随外键改动）
SET NULL（设空值）
SET DEFAULT（设默认值）
NO ACTION（无动作，默认的）

具体使用示例可以参考http://www.cppblog.com/wolf/articles/69089.html讲得很好。

四、外键使用过程出现的问题

我在使用外键的过程中遇到了几个问题，在这里讲下。

1. 没有使用InnoDB引擎

最初我忘了只有InnoDB才能使用外键的限制了，并且在使用MyISAM引擎建立外键的SQL语句，并不会报错，所以就没有发现。在做插入数据测试的时候才发现并没有进行参照完整性检查。

检查是否建立外键的一个方法是将建立数据表语句导出检查，可以采用phpMyAdmin，或者使用语句

show create tabale <table name>

所检查执行的SQL语句，可以看到显示出的建立数据表语句中根本没有包含建立外键语句。

2. 外键关系的两个表的列的数据类型不匹配

在修改完数据库引擎后，有遇到了一个问题，数据类型不匹配。运行建表SQL语句，只显示"ERROR 1005 (HY000): Can't create table 'geoinfosys.route' (errno: 150)”错误。检查一遍没有发现有任何错误。

这是使用命令：

mysql>SHOW ENGINE INNODB STATUS;

在"LATEST FOREIGN KEY ERROR"中显示了创建的错误。

我这里显示是：

Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint. Note that the internal storage type of ENUM and SET changed in tables created with >= InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables. See http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html for correct foreign key definition.

在仔细检查发现主表中的类型是 int unsigned，而从表中定义的类型是int，修改完就成功了。以后一定要注意仔细啊。