MySQL foreign key的几个注意点

本文主要是介绍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中测试都是这样。

这篇关于MySQL foreign key的几个注意点的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---