mysql概要（十四）(二)索引（补充：外键级联操作）

[ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]

[ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]

如果没有指定 ON DELETE 或 ON UPDATE，则默认为 NO ACTION。

此语句表示：

主表的主键发生删除或者更新（ON DELETE 或 ON UPDATE），

对于（cascade）对应从表外键对应的行会删除或外键值更新

对于（set null）对应从表外键值变更为null

对于（no action）有些特殊，如果在从表外键有对应的值，那么主表不能进行主键的更新和删除

这里以MySQL为例，总结一下3种外键约束方式的区别和联系。

这里以用户表和用户组表为例，这是一个典型的多对一关系，多个用户对应于一个用户组。
首先创建用户组表：

create table t_group (

id int not null,

name varchar(),

primary key (id)

);

并插入两条记录：

insert into t_group values (, 'Group1');

insert into t_group values (, 'Group2');

下面创建用户表，分别以不同的约束方式创建外键引用关系：
1、级联(cascade)方式

create table t_user (

id int not null,

name varchar(),

groupid int,

primary key (id),

foreign key (groupid) references t_group(id) on delete cascade on update cascade

);

参照完整性测试

insert into t_user values (, 'qianxin', ); #可以插入

insert into t_user values (, 'yiyu', ); #可以插入

insert into t_user values (, 'dai', ); #错误，无法插入，用户组3不存在，与参照完整性约束不符

约束方式测试

insert into t_user values (, 'qianxin', );

insert into t_user values (, 'yiyu', );

insert into t_user values (, 'dai', );

delete from t_group where id=; #导致t_user中的2、3记录级联删除

update t_group set id= where id=; #导致t_user中的1记录的groupid级联修改为2

2、置空(set null)方式

create table t_user (

id int not null,

name varchar(),

groupid int,

primary key (id),

foreign key (groupid) references t_group(id) on delete set null on update set null

);

参照完整性测试

insert into t_user values (, 'qianxin', ); #可以插入

insert into t_user values (, 'yiyu', ); #可以插入

insert into t_user values (, 'dai', ); #错误，无法插入，用户组3不存在，与参照完整性约束不符

约束方式测试

insert into t_user values (, 'qianxin', );

insert into t_user values (, 'yiyu', );

insert into t_user values (, 'dai', );

delete from t_group where id=; #导致t_user中的2、3记录的groupid被设置为NULL

update t_group set id= where id=; #导致t_user中的1记录的groupid被设置为NULL

3、禁止(no action / restrict)方式

create table t_user (

id int not null,

name varchar(),

groupid int,

primary key (id),

foreign key (groupid) references t_group(id) on delete no action on update no action

);

参照完整性测试

insert into t_user values (, 'qianxin', ); #可以插入

insert into t_user values (, 'yiyu', ); #可以插入

insert into t_user values (, 'dai', ); #错误，无法插入，用户组3不存在，与参照完整性约束不符

约束方式测试

insert into t_user values (, 'qianxin', );

insert into t_user values (, 'yiyu', );

insert into t_user values (, 'dai', );

delete from t_group where id=; #错误，从表中有相关引用，因此主表中无法删除

update t_group set id= where id=; #错误，从表中有相关引用，因此主表中无法修改

注：在MySQL中，restrict方式与no action方式作用相同。

来源：http://www.samool.com/archives/41586/

巴特西

mysql概要（十四）(二)索引（补充：外键级联操作）

这里以MySQL为例，总结一下3种外键约束方式的区别和联系。

最新文章

热门文章