mysql 外键

一、是什么

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

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

 

二、为什么

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

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

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

　　可以在定义从表外键的时候指定on update/on delete的方式，InnoDB支持５种方式：

  　　. 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;