MySQL如何添加外键

mysql中myisam和innodb存储引擎都支持外键（foreign key），但是myisam只能支持语法，却不能实际使用。下面通过例子记录下innodb中外键的使用方法：

创建主表：
mysql> create table parent(id int not null,primary key(id)) engine=innodb;
query ok, 0 rows affected (0.04 sec)

创建从表：
mysql> create table child(id int,parent_id int,foreign key (parent_id) references parent(id) on delete cascade) engine=innodb;
query ok, 0 rows affected (0.04 sec)
插入主表测试数据：
mysql> insert into parent values(1),(2),(3);
query ok, 3 rows affected (0.03 sec)
records: 3 duplicates: 0 warnings: 0
插入从表测试数据：
mysql> insert into child values(1,1),(1,2),(1,3),(1,4);
error 1452 (23000): cannot add or update a child row: a foreign key constraint fails (`test/child`, constraint `child_ibfk_1` foreign key (`parent_id`) references `parent` (`id`) on delete cascade)
因为4不在主表中，插入时发生了外键约束错误。
只插入前三条：
mysql> insert into child values(1,1),(1,2),(1,3);
query ok, 3 rows affected (0.03 sec)
records: 3 duplicates: 0 warnings: 0
成功！
删除主表记录，从表也将同时删除相应记录：
mysql> delete from parent where id=1;
query ok, 1 row affected (0.03 sec)
mysql> select * from child;
+------+-----------+
| id | parent_id |
+------+-----------+
| 1 | 2 |
| 1 | 3 |
+------+-----------+
2 rows in set (0.00 sec)

更新child中的外键，如果对应的主键不存在，则报错：
mysql> update child set parent_id=4 where parent_id=2;
error 1452 (23000): cannot add or update a child row: a foreign key constraint fails (`test/child`, constraint `child_ibfk_1` foreign key (`parent_id`) references `parent` (`id`) on delete cascade)




如果改为主表中存在的值，则可以正常更新：
mysql> update child set parent_id=2 where parent_id=2;
query ok, 0 rows affected (0.01 sec)
rows matched: 1 changed: 0 warnings: 0

如果要在父表中更新或者删除一行，并且在子表中也有一行或者多行匹配，此时子表的操作有5个选择：
· cascade: 从父表删除或更新且自动删除或更新子表中匹配的行。on delete cascade和on update cascade都可用。在两个表之间，你不应定义若干在父表或子表中的同一列采取动作的on update cascade子句。
· set null: 从父表删除或更新行，并设置子表中的外键列为null。如果外键列没有指定not null限定词，这就是唯一合法的。on delete set null和on update set null子句被支持。
· no action: 在ansi sql-92标准中，no action意味这不采取动作，就是如果有一个相关的外键值在被参考的表里，删除或更新主要键值的企图不被允许进行（gruber, 掌握sql, 2000:181）。 innodb拒绝对父表的删除或更新操作。
· restrict: 拒绝对父表的删除或更新操作。no action和restrict都一样，删除on delete或on update子句。（一些有延期检查，并且no action是一个延期检查。在mysql中，外键约束是被立即检查的，所以no action和restrict是同样的）。
· set default: 这个动作被解析程序识别，但innodb拒绝包含on delete set default或on update set default子句的表定义。

像mysql这样的关系型数据库管理系统，它们的基础是在数据库的表之间创建关系的能力。通过方便地在不同表中建立记录到记录的联系，rdbms可以利用不同的方法分析数据，同时保持数据库以系统的方式、最小的冗余进行组织。

简单描述：
这些关系基本上依靠外键进行管理，在关系中所有表中具有相同含义的字段作为公共部分来连接不同表中的记录。外键可以是一对一的，一个表的记录只能与另一个表的一条记录连接，或者是一对多的，一个表的记录与另一个表的多条记录连接。

mysql中“键”和“索引”的定义相同， 所以外键和主键一样也是索引的一种。不同的是mysql会自动为所有表的主键进行索引，但是外键字段必须由用户进行明确的索引。这和一些封建思想比较沉重的家庭是一样的，外来的孩子(儿媳妇，倒插门女婿)一般都是不受重视的。

低俗示例：

表间一对一关系示例：

有两张表，第一张表是记录公司有多少人，都有谁，也就是员工编号及员工姓名这些基本表。另一张表记录每个月发给用户多少工资，所谓工资表是也。

但是工资表里面不能以员工姓名为主键，同样要通过员工id，因为员工的姓名是可能重复的啊。部门经理叫张三，小弟也叫张三，那这俩张三的工资能一样吗？并且员工表里面的每个人都有工资，否则谁也不给你干活，且一个人只能有一份工资，否则老板也不同意了。所以员工表和工资表是通过员工id进行关联的一对一关系。

不过我们要有一个好的价值观，我们上班不能为了钱，我们是为了学知识，学文化，为早日实现四个现代化(别问我是啥，也别问我到底实现没有)而努力奋斗。所以在工资表里如果没有你也不要乱喊。嗯。

/*
建立员工表
*/
createtableemployees(
idint(5)notnullauto_increment,
namevarchar(8)notnull,
primary key(id)
)
type＝innodb;

/*
建立工资表
*/
createtablepayroll(
idint(5)notnull,
emp_idint(5)notnull,
namevarchar(8)notnull,
payrollfloat(4,2)notnull,
primary key(id),
indexemp_id(emp_id),
foreign key(emp_id)referencesemployees(id)
)
type=innodb;

表间一对多关系示例：

有两个表，一个是贪官表，有贪官的id和名字。另有一张贪官情妇表，注意一个贪官不一定只有一个情妇，其有个二三四五奶是很正常的，所以在贪官表里面的一条数据，对应情妇表里可能就有多条记录，这是通过贪官id进行关联的一对多关系。

参照完整性：

当外键与另一个表的字段有关系，而且这种关系是惟一时，这个系统就称为处于参照完整性的状态。也就是说，如果一个字段在所有的表中只出现一次，而且每个表的这个字段的变化都会影响其他表，这就是存在参照完整性。

术语理解上可能不太方便，其实就是说要在有外键的表中保持所有数据的一致性。比如说“张三”离职了，在员工表里面肯定没有这个人了，可是如果在工资表里面还存在这个孩子，那么老大就会很生气的。

另外，比如说一个县官，因为一些小政绩，由县官变成了知府，那么他的那些情妇的地位也要调整一下，最起码得从县官二奶改为知府二奶，否则这位二奶也是不会同意的。

mysql的外键只能在innodb表中使用：

当今主流数据库都会自动考虑参照完整性的问题。当你更新或删除数据时，其会把相关联的表中数据也都给你变过来。比如县官张三改名为王二麻子，其情妇的称号就会自动改为王二麻子的情妇。嗯。

mysql对此一直持观望态度，它允许使用外键，但是为了完整性检验的目的，在除了innodb表类型之外的所有表类型中都忽略了这个功能。这可能有些怪异，实际上却非常正常：对于数据库的所有外键的每次插入、更新和删除后，进行完整性检查是一个耗费时间和资源的过程，它可能影响性能，特别是当处理复杂的或者是缠绕的连接树时。因而，用户可以在表的基础上，选择适合于特定需求的最好结合。。

所以，如果需要更好的性能，并且不需要完整性检查，可以选择使用myisam表类型，如果想要在mysql中根据参照完整性来建立表并且希望在此基础上保持良好的性能，最好选择表结构为innodb类型。

mysql创建外键语法：

创建外键的语法是这样的：foreign key (当前表的字段名)…references参照表(参照表的字段名)

foreign key (emp_id) references employees (id);的意思就是说当前表的emp_id字段是以employees的id字段为外键的。

注意事项：

一旦建立外键，mysql只允许向当前表中加入外键表中已有的数据列。比如说贪官表里有“王二麻子”，那么在情妇表只才能有“王二麻子的情妇”。也就是说只有确认一个人是贪官了，才能把其情妇信息列入此表中，否则是不行滴。关系中的所有表必须是innodb表，在非innodb表中，mysql将会忽略foreign key…references修饰符。用于外键关系的字段必须在所有的参照表中进行明确地索引，innodb不能自动地创建索引。在外键关系中，字段的数据类型必须相似，这对于大小和符号都必须匹配的整数类型尤其重要。即使表存在外键约束，mysql还允许我们删除表，并且不会产生错误(即使这样做可能会破坏更早创建的外键)

删除外键方法：

long long ago，人们只能通过删除表来删除外键。不过现在mysql(在4.0.13及更高版本中)提供了一种从表中删除外键比较缓和的方法，缓和与否不太清楚，但是至少不再那么无耻。

alter table table-name drop foreign key key-id;

这里有一个概念，这个外键的id是啥玩意？我们可以通过show　create tablｅ 命令来获得key-id的值。日后我们详细讨论这些内容，大家可以自行演示。

/*
显示建表结构语句，key-id为payroll_ibfk_1
*/
showcreatetablepayroll \g
/*
*************************** 1. row ***************************
table: payroll
create table: create table `payroll` (
`id` int(5) not null,
`emp_id` int(5) not null,
`name` varchar(8) not null,
`payroll` float(4,2) not null,
primary key (`id`),
key `emp_id` (`emp_id`),
constraint `payroll_ibfk_1` foreign key (`emp_id`) references `employees` (`id`)
) engine=innodb default charset=latin1
1 row in set (0.00 sec)

*/

自动键更新和删除：

外键可以保证新插入的记录的完整性。但是，如果在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规则连接时，任意一个主表的变化都会导致甚至只和原始删除有一些将要联系的记录在没有警告的情况下被删除。所以，我们在操作之前还是要检查这些规则的，操作之后还要再次检查。