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

最新推荐文章于 2023-11-06 19:59:36 发布

转载最新推荐文章于 2023-11-06 19:59:36 发布 · 80 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/JiangLe/p/9594336.html

文章标签：

#数据库

本文介绍标准SQL中外键约束的应用，包括子表引用父表的主键和唯一索引，以及InnoDB在此基础上所做的扩展，允许子表引用父表上的任何索引列。

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