【标准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的方式。
【学习交流】
-----------------------------http://www.sqlpy.com-------------------------------------------------
-----------------------------http://www.sqlpy.com-------------------------------------------------