MySQL 主外键约束与标准SQL不同的地方

【标准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-------------------------------------------------