51dev.com IT技术开发者社区

51dev.com 技术开发者社区

mysql 外键

代码星球阅读(46)2020-10-14 收藏0次评论

一、是什么

  从表的某列指向主表的某列,从表的这一列就是从表的外键。

    外键不一定要指向主表的主键,但必须是唯一性索引。

 

二、为什么

  外键能保持数据的一致性、完整性。

  1、外键约束用于预防破坏表之间连接的行为:在主表上直接update/delete以更新或删除在从表中有匹配行的外键时,会报错

  2、外键约束也能防止非法数据插入外键列,因为它必须是它指向的主表的列中的值之一:如果在主表列字段找不到对应的键,则不允许在从表上进行insert/update

  可以在定义从表外键的时候指定on update/on delete的方式,InnoDB支持5种方式:

    . cascade方式 
     在父表上update/delete记录时,同步update/delete掉子表的匹配记录 
     On delete cascade从mysql3.23.50开始可用; on update cascade从mysql4.0.8开始可用 

    . set null方式 
     在父表上update/delete记录时,将子表上匹配记录的列设为null 
     要注意子表的外键列不能为not null 
     On delete set null从mysql3.23.50开始可用; on update set null从mysql4.0.8开始可用 

    . No action方式 
     如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作 
     这个是ANSI SQL-92标准,从mysql4.0.8开始支持 

    . Restrict方式 (默认)
     同no action, 都是立即检查外键约束 

    . Set default方式 
     解析器认识这个action,但Innodb不能识别,不知道是什么意思... 
  
    注意:trigger不会受外键cascade行为的影响,即不会解发trigger 

 

三、怎么用

  外键的使用条件:
  1.两个表必须是InnoDB表,MyISAM表暂时不支持外键(据说以后的版本有可能支持,但至少目前不支持);
  2.外键列必须建立了索引,MySQL 4.1.2以后的版本在建立外键时会自动创建索引,但如果在较早的版本则需要显示建立; 
  3.外键关系的两个表的列必须是数据类型相似,也就是可以相互转换类型的列,比如int和tinyint可以,而int和char则不可以;

  

   (1)创建表

create table user_info
(
user_id int not  null,
name varchar (100) not null,
primary key (user_id),
foreign key (name) references user (name)
)

  user表的name列并不是user表的主键,但是它是唯一的,这样也可以创建外键。如果被引用的列不是唯一的,则会报错:Error Code: 1215. Cannot add foreign key constraint

 

  mysql会为user表的name列自动创建一个唯一性索引

  也会为user_info表的name列创建一个索引,因为user_info表的name列并不是唯一的,因此不会创建唯一性索引,这也证明从表的外键不一定需要是唯一性索引

  在user_info表插入数据时,name字段的值需要在user的name字段有对应的值,这是防止非法数据插入外键列。如果没有则会报错:

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`mychat`.`user_info`, CONSTRAINT `user_info_ibfk_1` FOREIGN KEY (`name`) REFERENCES `user` (`name`))

 

  如果在user_info的name列有相应数据,无法删除user中的数据,这是预防破坏表之间连接的行为:

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`mychat`.`user_info`, CONSTRAINT `user_info_ibfk_1` FOREIGN KEY (`name`) REFERENCES `user` (`name`))

 

   (2)直接添加约束

alter table mychat.user_info add foreign key (user_id) references user(user_id);

    

 (3)撤销外键约束

alter table mychat.user_info drop foreign key user_info_ibfk_3;

 

以上就是mysql 外键的全部内容。