MySql 3种方式添加外键约束详解
2015-07-17 12:13:08  By: dwtedx

MySQL外键约束方式

简单的说、如果表A的主关键字是表B中的字段、则该字段称为表B的外键、表A称为主表、表B称为从表、外键是用来实现参照完整性的、不同的外键约束方式将可以使两张表紧密的结合起来、特别是修改或者删除的级联操作将使得日常的维护工作更加轻松


这里以MySQL为例、总结一下3种外键约束方式的区别和联系、 这里以用户表和用户组表为例、这是一个典型的多对一关系、多个用户对应于一个用户组


首先创建用户组表

create table group ( 
	id int not null, 
	name varchar(30), 
	primary key (id) 
); 


并插入两条记录

insert into group values (1, 'Group1'); 
insert into group values (2, 'Group2'); 


下面创建用户表(从表)、分别以不同的约束方式创建外键引用关系


1、级联(cascade)方式、级联方式 

create table t_user ( 
	id int not null, 
	name varchar(30), 
	groupid int, 
	primary key (id), 
	foreign key (groupid) 
	references t_group(id) 
	on delete cascade 
	on update cascade 
); 


参照完整性测试

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


约束方式测试

insert into t_user values (1, 'qianxin', 1); 
insert into t_user values (2, 'yiyu', 2); 
insert into t_user values (3, 'dai', 2); 

#导致t_user中的2、3记录级联删除 
delete from t_group where id=2;
#导致t_user中的1记录的groupid级联修改为2
update t_group set id=2 where id=1;


2、置空(set null)方式、置空方式

create table t_user ( 
	id int not null, 
	name varchar(30), 
	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 (1, 'qianxin', 1);
#可以插入 
insert into t_user values (2, 'yiyu', 2);
#错误、无法插入、用户组3不存在、与参照完整性约束不符 
insert into t_user values (3, 'dai', 3);


约束方式测试

insert into t_user values (1, 'qianxin', 1); 
insert into t_user values (2, 'yiyu', 2); 
insert into t_user values (3, 'dai', 2); 

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


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

create table t_user ( 
	id int not null, 
	name varchar(30), 
	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 (1, 'qianxin', 1);
#可以插入 
insert into t_user values (2, 'yiyu', 2);
#错误、无法插入、用户组3不存在、与参照完整性约束不符 
insert into t_user values (3, 'dai', 3);


约束方式测试

insert into t_user values (1, 'qianxin', 1); 
insert into t_user values (2, 'yiyu', 2); 
insert into t_user values (3, 'dai', 2); 

#错误、从表中有相关引用、因此主表中无法删除 
delete from t_group where id=2;
#错误、从表中有相关引用、因此主表中无法修改 
update t_group set id=2 where id=1;


注:在MySQL中、restrict方式与no action方式作用相同


若资源对你有帮助、浏览后有很大收获、不妨小额打赏我一下、你的鼓励是维持我不断写博客最大动力

想获取DD博客最新代码、你可以扫描下方的二维码、关注DD博客微信公众号(ddblogs)

或者你也可以关注我的新浪微博、了解DD博客的最新动态:DD博客官方微博(dwtedx的微博)

如对资源有任何疑问或觉得仍然有很大的改善空间、可以对该博文进行评论、希望不吝赐教

为保证及时回复、可以使用博客留言板给我留言: DD博客留言板(dwtedx的留言板)

感谢你的访问、祝你生活愉快、工作顺心、欢迎常来逛逛


快速评论


技术评论

  • 该技术还没有评论、赶快抢沙发吧...
DD记账
top
+