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

mysql 外键约束

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

① 外键只能指向主表或本表的主键列或者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 (1qianxin1); #可以插入   insert into t_user values (2yiyu2); #可以插入  insert into t_user values (3dai3); #无法插入,外键3不存在,与参照完整性约束不符 #约束方式测试: insert into t_user values (1qianxin1);    insert into t_user values (2yiyu2);   insert into t_user values (3dai2);  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 (1qianxin1); #可以插入   
insert into t_user values (2yiyu2); #可以插入  insert into t_user values (3dai3); #无法插入,外键3不存在,与参照完整性约束不符  #约束方式测试  insert into t_user values (1qianxin1);    insert into t_user values (2yiyu2);    insert into t_user values (3dai2);  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 外键约束