【标准SQL的外键约束条件】
1):子表引用父表的主键
drop table if exists child,parent;create table if not exists parent(id int not null auto_increment primary key,v int);create table if not exists child(id int not null auto_increment primary key,parent_id int not null,v int,constraint fk__child__parent_id foreign key (parent_id) references parent(id) );insert into parent(id,v) values(1,100);insert into child(parent_id,v) values(1,1000);insert into child(parent_id,v) values(1,1000);select * from parent;+----+------+| id | v |+----+------+| 1 | 100 |+----+------+select * from child;+----+-----------+------+| id | parent_id | v |+----+-----------+------+| 1 | 1 | 1000 || 2 | 1 | 1000 |+----+-----------+------+
2):子表引用交表的唯一索引
create table if not exists parent(id int not null,v int,constraint unique index uix__parent_id (id));create table if not exists child(id int not null auto_increment primary key,parent_id int not null,v int,constraint fk__child__parent_id foreign key (parent_id) references parent(id) );insert into parent(id,v) values(1,100);insert into child(parent_id,v) values(1,1000);insert into child(parent_id,v) values(1,1000);select * from parent;+----+------+| id | v |+----+------+| 1 | 100 |+----+------+select * from child;+----+-----------+------+| id | parent_id | v |+----+-----------+------+| 1 | 1 | 1000 || 2 | 1 | 1000 |+----+-----------+------+
【innodb在标准SQL上做的扩展】
1):只要在父表上有在对应的列上建索引，那么这个列就能在子表中引用
create table if not exists parent(id int not null auto_increment primary key,v int,index uix__parent_v (v) -- 只要父表上有索引就行 );create table if not exists child(id int not null auto_increment primary key,parent_v int not null,v int,constraint fk__child__parent_v foreign key (parent_v) references parent(v) -- 在子表中引用 );insert into parent(id,v) values(1,100);insert into parent(id,v) values(2,100);insert into child(parent_v,v) values(100,2000);insert into child(parent_v,v) values(100,2000);select * from parent;+----+------+| id | v |+----+------+| 1 | 100 || 2 | 100 |+----+------+select * from child;+----+----------+------+| id | parent_v | v |+----+----------+------+| 1 |100 | 2000 || 2 |100 | 2000 |+----+----------+------+
【我的评介】
主外键约束在标准SQL下体现的是一种一对多的关系，但是经过MySQL的拓展之后可以表现出“多对多”的关系；虽然MySQL这样
的设计有一定的灵活性，个人觉得最好还是使用标准SQL的方式。
【学习交流】
------------------------------------------------------------------------------
------------------------------------------------------------------------------
如果觉得《MySQL 主外键约束与标准SQL不同的地方》对你有帮助，请点赞、收藏，并留下你的观点哦！
