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-------------------------------------------------