MySQL foreign key的几个注意点

1 mysql5.6.24 innodb中若直接在定义列的时候加上references parentTable(col)并不能创建外键，需要单独的foreign key句子。

例子：

create table t1(id int primary key)engine=innodb;
create table t2(id int references t1(id) ON DELETE CASCADE ON UPDATE CASCADE)engine=innodb;no
create table t3(id int unique references t1(id) ON DELETE CASCADE ON UPDATE CASCADE)engine=innodb;no
create table t4(id int primary key references t1(id) ON DELETE CASCADE ON UPDATE CASCADE)engine=innodb;no
create table t5(id int,foreign key(id) references t1(id) ON DELETE CASCADE ON UPDATE CASCADE)engine=innodb;ok

2 被参照的列(簇)需要是某个索引的第一个列(簇)

例子：

create table t1(id1 int,id2 int,id3 int,primary key(id1,id2,id3),index(id3,id2))engine=innodb;
create table t2(id1 int,foreign key(id1) references t1(id1))engine=innodb;ok
create table t3(id3 int,foreign key(id3) references t1(id3))engine=innodb;ok
create table t4(id2 int,foreign key(id2) references t1(id2))engine=innodb;can't add foreign key

官方文档：

      MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. 

3 myisam表不能创建外键

例子：

create table t1(id int primary key)engine=myisam;

create table t2(id int,foreign key(id) references t1(id))engine=myisam; no foreign key

在mysql5.6.24和mysql5.1.73中测试都是这样。