SQL -- foreign key

table if exists test1; create table test1 ( id int auto_increment primary key, name varchar(20) NOT NULL ); drop table if exists test2; create table test2 ( # error 150, 错误 类型不同 id2 smallint primary key auto_increment, name2 varchar(10) not null, foreign key (id2) references test1 (id) );

drop table if exists test2;
create table test2 (
id2 int auto_increment primary key,
name2 varchar(10) not null,
foreign key (id2) references test1 (id)
);

父表: test1
子表: test2

foreign key 默认: 父表和子表的行为

on update / on delete -—

cascade	从父表删除或者更新且 自动删除或者更新子表 中匹配的行
set null	从父表删除或者更新行, 并设置子表中的外键列为 NULL
restrict	拒绝对父表的删除或者更新操作
no action	标准 sql 关键字, 和 restrict (mysql 特有) 相同 (是默认值)

create table test3 (
id3 int primary key auto_increment,
name3 varchar(10) not null,
foreign key (id3) references test1 (id) on delete cascade on update cascade
);

父表和子表同步的例子

准备

drop table if exists test1;
drop table if exists test2;

create table test1 (
id int NOT NULL primary key,
sex enum(‘f‘, ‘m‘)
);

create table test2 (
id int not null,
name varchar(10),
foreign key (id) references test1(id) on delete cascade on update cascade
);

insert into test1 (id, sex) values (1, ‘f‘), (2, ‘m‘), (3, ‘f‘);

select * from test1;

| id | sex  |
+----+------+
|  1 | f    |
|  2 | m    |
|  3 | f    |

select * from test2;

Empty set (0.00 sec)

insert into test2 (id, name) values
(1, ‘abc‘), (1, ‘def‘), (1, ‘xyz‘),
(2, ‘ghi‘), (2, ‘jkl‘), (2, ‘opq‘),
(3, ‘lmn‘), (3, ‘uvw‘), (3, ‘rst‘);

select * from test2;

| id | name |
+----+------+
|  1 | abc  |
|  1 | def  |
|  1 | xyz  |
|  2 | ghi  |
|  2 | jkl  |
|  2 | opq  |
|  3 | lmn  |
|  3 | uvw  |
|  3 | rst  |

delete from test1 where id=1;

select * from test2;

| id | name |
+----+------+
|  2 | ghi  |
|  2 | jkl  |
|  2 | opq  |
|  3 | lmn  |
|  3 | uvw  |
|  3 | rst  |

drop table test1, test2;

SQL -- foreign key

标签：