首页 > 代码库 > mysql 外键约束

mysql 外键约束

2024-08-31 05:59:24   222人阅读

外键的作用：外键具有保持数据完整性和一致性的机制。

① 外键只能指向主表或本表的主键列或者unique
② 外键的数据类型应该与它指向的列类型一致
③ 外键的值：NULL 或者 指向列中存在的值

##############准备##############

创建一个组表作为主表，命名为t_group：

create table t_group(
    id int not null,
    name varchar(30),
    primary key(id)
);
#插入两条记录
insert into t_group values (1, ‘Group1‘); 
insert into t_group values (2, ‘Group2‘);

##############演示##############

三种外键约束模式：级联（cascade）、置空（set null）、禁止（no action / restrict 默认）

1.级联方式（从表中的列也会被删除或更新）

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  
 );
#（1）从表中的外键值必须在主表中存在
#（2）从表中的外键类型一定要和参照键（主表中的主键）类型一致，若为数#字，则要完全一致；若为字符，字符个数可以不一致。

#参照完整性测试：
insert into t_user values (1, ‘qianxin‘, 1); #可以插入  
insert into t_user values (2, ‘yiyu‘, 2); #可以插入 
insert into t_user values (3, ‘dai‘, 3); #无法插入，外键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; #导致t_user中的2、3记录级联删除 
update t_group set id=2 where id=1; #导致t_user中的1记录的groupid级联修改为2 

2.置空方式（主表中的记录被删除或主键被更新，从表则会被设置为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); #可以插入 
insert into t_user values (3, ‘dai‘, 3); #无法插入，外键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; #导致t_user中的2、3记录的groupid被设置为NULL   
update t_group set id=2 where id=1; #导致t_user中的1记录的groupid被设置为NULL 

3.禁止方式（从表有引用时，主表无法删除或更新）

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); #可以插入    
insert into t_user values (3, ‘dai‘, 3); #无法插入，外键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; #错误，从表中有相关引用，因此主表中无法修改

注意：外键约束需要默认引擎为innodb引擎。

linux环境下可以通过修改配置文件/usr/share/mysql/my-default.cnf来修改默认引擎

mysql 外键约束


联系
我们