MySQL之外键的使用

一、概述

• MySQL 外键约束经常与主键约束一起使用。对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）；
• 两个表必须是InnoDB表，MyISAM表暂时不支持外键；
• 外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列；
• 主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的；

二、语法

[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…] REFERENCES <主表名> 主键列1 [，主键列2，…]

另外，创建外键时再后边还可以设置更删时策略（CASCADE、NO ACTION、RESTRICT、SET NULL）

二、示例

建主表

CREATE TABLE `testtab` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(255) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

建从表

CREATE TABLE `testtab1` (`pid` int(11) NOT NULL,`phone` varchar(20) DEFAULT NULL,`salary` int(10) DEFAULT NULL,KEY `id` (`pid`),CONSTRAINT `id` FOREIGN KEY (`pid`) REFERENCES `testtab` (`id`) ON DELETE CASCADE ON UPDATE CASCADE  # DELETE CASCADE ON UPDATE CASCADE 更删级联) ENGINE=InnoDB DEFAULT CHARSET=utf8;

向主从表添加数据

可以看到，向从表中添加pid=3的数据报错，这是因为主表中找不到id=3的数据

mysql> select * from testtab;+----+------+| id | name |+----+------+|  1 | Tom  ||  2 | Ken  |+----+------+2 rows in set (0.02 sec)mysql> insert into testtab1 values(1, \'17694561200\', 12000);Query OK, 1 row affected (0.09 sec)mysql> insert into testtab1 values(3, \'15623412011\', 10000);1452 - Cannot add or update a child row: a foreign key constraint fails (`spring`.`testtab1`, CONSTRAINT `id` FOREIGN KEY (`pid`) REFERENCES `testtab` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)mysql> select * from testtab1;+-----+-------------+--------+| pid | phone       | salary |+-----+-------------+--------+|   1 | 17694561200 |  12000 |+-----+-------------+--------+1 row in set (0.02 sec)mysql>

删除表数据

设置了更删级联后，在对主表数据删除时会同时删除从表中关联的数据，对从表删除时不会影响到主表
反之不设置，则如果要删除的主表数据在对应从表有记录则会报错（Cannot delete or update a parent row: a foreign key constraint fails），删除的从表中未关联的可以删除ok；直接删除从表数据也是ok的

mysql> delete from testtab where id = 1;Query OK, 1 row affected (0.06 sec)mysql> select * from testtab;+----+------+| id | name |+----+------+|  2 | Ken  |+----+------+1 row in set (0.06 sec)mysql> select * from testtab1;Empty setmysql> insert into testtab1 values(2, \'17694561200\', 12000);Query OK, 1 row affected (0.08 sec)mysql> delete from testtab where name=\'ken\';Query OK, 1 row affected (0.03 sec)mysql> select * from testtab1;Empty setmysql>