mysql笔记3 外键

常见错误

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails

当前表如果插入某条记录时检查到foreign key中指定的值在该外键所在的表中不存在的话，是不允许插入的。即MySQL只允许向当前表中加入外键表中已有的数据列。比如说外键贪官表里有“王二麻子”，那么在当前情妇表只才能有“王二麻子的情妇”。也就是说只有确认了外键贪官表，才能把其情妇信息列入当前表中。一对一的关系

示例

1）外键表

create table repo_table(

repo_id char(13) not null primary key,

repo_name char(14) not null)

type=innodb;

当前表

mysql> create table busi_table(

    -> busi_id char(13) not null primary key,

    -> busi_name char(13) not null,

    -> repo_id char(13) not null,

    -> foreign key(repo_id) references repo_table(repo_id))  

-> type=innodb;

2）插入数据

insert into repo_table values("12","sz"); //success

insert into repo_table values("13","cd"); //success

insert into busi_table values("1003","cd", "13"); //success

insert into busi_table values("1002","sz", "12"); //success

insert into busi_table values("1001","gx", "11"); //failed,提示：

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`smb_man`.`busi_table`, CONSTRAINT `busi_table_ibfk_1` FOREIGN KEY (`repo_id`) REFERENCES `repo_table` (`repo_id`))

ALTER TABLE table-name DROP FOREIGN KEY key-id; 删除外键

自动键更新和删除：

外键可以保证新插入的记录的完整性。但是，如果在REFERENCES从句中从已命名的表删除记录会怎样？在使用同样的值作为外键的辅助表中会发生什么？

很明显，那些记录也应该被删除，否则在数据库中就会有很多无意义的孤立记录。MySQL可能通过向FOREIGN KEY…REFERENCES 修饰符添加一个ON DELETE或ON UPDATE子句简化任务，它告诉了数据库在这种情况如何处理孤立任务。

关键字	含义
CASCADE	删除包含与已删除键值有参照关系的所有记录
SET NULL	修改包含与已删除键值有参照关系的所有记录，使用NULL值替换(只能用于已标记为NOT　NULL的字段)
RESTRICT	拒绝删除要求，直到使用删除键值的辅助表被手工删除，并且没有参照时(这是默认设置，也是最安全的设置)
NO ACTION	啥也不做

请注意，通过 ON UPDATE　和ON DELETE规则，设置ＭｙＳＱＬ能够实现自动操作时，如果键的关系没有设置好，可能会导致严重的数据破坏。例如，如果一系列的表通过外键关系和ON DELETE CASCADE 规则连接时，任意一个主表的变化都会导致甚至只和原始删除有一些将要联系的记录在没有警告的情况下被删除。所以，我们在操作之前还是要检查这些规则的，操作之后还要再次检查。