mysql外键总结

目录

简介

外键的规则

主键、外键和唯一索引的区别

语法

在创建表时设置外键约束

在修改表时添加外键约束

删除外键约束

外键级联

级联的优势与劣势

---

简介

MySQL 外键约束（FOREIGN KEY）是表的一个特殊字段，经常与主键约束一起使用。对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）。

外键用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性。比如，一个水果摊，只有苹果、桃子、李子、西瓜等 4 种水果，那么，你来到水果摊要买水果就只能选择苹果、桃子、李子和西瓜，其它的水果都是不能购买的。

主表删除某条记录时，从表中与之对应的记录也必须有相应的改变。一个表可以有一个或多个外键，外键可以为空值，若不为空值，则每一个外键的值必须等于主表中主键的某个值。

外键的规则

定义外键时，需要遵守下列规则：

父表和子表必须使用相同的存储引擎，而且禁止使用临时表。具有外键列的表称为子表；子表所参照的表称为父表。

数据表的存储引擎只能为 InnoDB。

主表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则主表与从表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。

必须为主表定义主键。

在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键。

主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。

外键中列的数目必须和主表的主键中列的数目相同。

外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同； 而字符的长度则可以不同。加 FOREIGN KEY 关键字的列称为外键列；外键列所参照的列称为参照列。  

外键列和参照列必须创建索引。如果外键列不存在索引的话，MySQL 将自动创建索引。 如果参照列不存在索引的话，MySQL 不会自动创建索引。MySQL 会为主键自动创建索引。

主键、外键和唯一索引的区别

定义

主键  唯一标识一条记录，不能有重复的，不允许为空
外键  表的外键是另一表的主键, 外键可以有重复的, 可以是空值
唯一索引  该字段没有重复值，但可以有一个空值

作用

主键  用来保证数据完整性
外键  用来和其他表建立联系用的
唯一索引  是提高查询排序的速度

个数

主键  主键只能有一个
外键  一个表可以有多个外键
唯一索引  一个表可以有多个唯一索引

语法

在创建表时设置外键约束

在 CREATE TABLE 语句中，通过 FOREIGN KEY 关键字来指定外键，具体的语法格式如下：

[CONSTRAINT <外键名>] 

FOREIGN KEY 字段名 [，字段名2，…]

REFERENCES <主表名> 主键列1 [，主键列2，…]

[ON DELETE reference_option]

[ON UPDATE reference_option]

reference_option:

RESTRICT | CASCADE | SET NULL | NO ACTION

为了展现表与表之间的外键关系，本例在 test_db 数据库中创建一个部门表 tb_dept1，表结构如下表所示。

字段名称    数据类型    备注
id    INT(11)    部门编号
name    VARCHAR(22)    部门名称
location    VARCHAR(22)    部门位置
创建 tb_dept1 的 SQL 语句和运行结果如下所示。
mysql> CREATE TABLE tb_dept1
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(22) NOT NULL,
    -> location VARCHAR(50)
    -> );
Query OK, 0 rows affected (0.37 sec)

创建数据表 tb_emp6，并在表 tb_emp6 上创建外键约束，让它的键 deptId 作为外键关联到表 tb_dept1 的主键 id，SQL 语句和运行结果如下所示。

mysql> CREATE TABLE tb_emp6
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT,
    -> CONSTRAINT fk_emp_dept1
    -> FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
    -> );
Query OK, 0 rows affected (0.37 sec)

mysql> DESC tb_emp6;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  | MUL | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (1.33 sec)

以上语句执行成功之后，在表 tb_emp6 上添加了名称为 fk_emp_dept1 的外键约束，外键名称为 deptId，其依赖于表 tb_dept1 的主键 id。

注意：从表的外键关联的必须是主表的主键，且主键和外键的数据类型必须一致。例如，两者都是 INT 类型，或者都是 CHAR 类型。如果不满足这样的要求，在创建从表时，就会出现“ERROR 1005(HY000): Can't create table”错误。

在修改表时添加外键约束

外键约束也可以在修改表时添加，但是添加外键约束的前提是：从表中外键列中的数据必须与主表中主键列中的数据一致或者是没有数据。

在修改数据表时添加外键约束的语法格式如下：
ALTER TABLE <数据表名> ADD CONSTRAINT <外键名>
FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>);修改数据表 tb_emp2，将字段 deptId 设置为外键，与数据表 tb_dept1 的主键 id 进行关联，SQL 语句和运行结果如下所示。

mysql> ALTER TABLE tb_emp2
    -> ADD CONSTRAINT fk_tb_dept1
    -> FOREIGN KEY(deptId)
    -> REFERENCES tb_dept1(id);
Query OK, 0 rows affected (1.38 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE tb_emp2\G
*************************** 1. row ***************************
       Table: tb_emp2
Create Table: CREATE TABLE `tb_emp2` (
  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  `deptId` int(11) DEFAULT NULL,
  `salary` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_tb_dept1` (`deptId`),
  CONSTRAINT `fk_tb_dept1` FOREIGN KEY (`deptId`) REFERENCES `tb_dept1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.12 sec)

注意：在为已经创建好的数据表添加外键约束时，要确保添加外键约束的列的值全部来源于主键列，并且外键列不能为空。

删除外键约束

当一个表中不需要外键约束时，就需要从表中将其删除。外键一旦删除，就会解除主表和从表间的关联关系。

删除外键约束的语法格式如下所示：

ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;删除数据表 tb_emp2 中的外键约束 fk_tb_dept1，SQL 语句和运行结果如下所示。

mysql> ALTER TABLE tb_emp2
    -> DROP FOREIGN KEY fk_tb_dept1;
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE tb_emp2\G
*************************** 1. row ***************************
       Table: tb_emp2
Create Table: CREATE TABLE `tb_emp2` (
  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  `deptId` int(11) DEFAULT NULL,
  `salary` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_tb_dept1` (`deptId`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.00 sec)

可以看到，tb_emp2 中已经不存在 FOREIGN KEY，原有的名称为 fk_emp_dept 的外键约束删除成功。

外键级联

reference_option:

RESTRICT | CASCADE | SET NULL | NO ACTION

如果子表试图创建一个在父表中不存在的外键值，InnoDB会拒绝任何INSERT或UPDATE操作。如果父表试图UPDATE或者DELETE任何子表中存在或匹配的外键值，最终动作取决于外键约束定义中的ON UPDATE和ON DELETE选项。

InnoDB支持5种不同的动作，如果没有指定ON DELETE或者ON UPDATE，默认的动作为RESTRICT:

1. CASCADE: 从父表中删除或更新对应的行，同时自动的删除或更新自表中匹配的行。ON DELETE CANSCADE和ON UPDATE CANSCADE都被InnoDB所支持。

2. SET NULL: 从父表中删除或更新对应的行，同时将子表中的外键列设为空。注意，这些在外键列没有被设为NOT NULL时才有效。ON DELETE SET NULL和ON UPDATE SET SET NULL都被InnoDB所支持。

3. NO ACTION: InnoDB拒绝删除或者更新父表。

4. RESTRICT: 拒绝删除或者更新父表。指定RESTRICT(或者NO ACTION)和忽略ON DELETE或者ON UPDATE选项的效果是一样的。

5. SET DEFAULT: InnoDB目前不支持。

外键约束使用最多的两种情况：

1)父表更新时子表也更新，父表删除时如果子表有匹配的项，删除失败；

2)父表更新时子表也更新，父表删除时子表匹配的项也删除。

前一种情况，在外键定义中，我们使用ON UPDATE CASCADE ON DELETE RESTRICT；

后一种情况，可以使用ON UPDATE CASCADE ON DELETE CASCADE。

如果主从表只是普通的外键约束，则在更新/删除主表记录时，需要先更新/删除从表记录，否则将会报外键约束异常，导致操作失败，如下：

 Cannot delete or update a parent row: a foreign key constraint fails (`test`.`goods`, CONSTRAINT `goods_ibfk_1` FOREIGN KEY (`shop_id`) REFERENCES `shop` (`id`))

级联的优势与劣势

级联的优势：少写对应的update和delete语句

下面说说级联的劣势：

在你的工程足够大的时候，这样的删除会很慢，实质上造成了表与表之间的耦合。远远比不删新增加一个isDel的项，使用标志删除的方式，查表的时候仅查询isDel=false的项。其实delete语句在网页的编程的时间根本就是可以扔掉的。这样还有个好处，出现在需要找被删除的旧数据的时候，绝对可以找回来。

这样你的网页的运行速度会大大加快，否则如果一旦执行级联删除的语句，会涉及的表足够多的时候，执行起来将会足够慢。