MySQL 支持外键,允许跨表交叉引用相关数据,以及外键约束,这有助于保持相关数据的一致性。
外键关系涉及包含初始列值的父表,以及包含引用父列值的列值的子表。在子表上定义了外键约束。
CREATE TABLE
在or
语句
中定义外键约束的基本语法ALTER TABLE
包括以下内容:
[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (col_name, ...)
REFERENCES tbl_name (col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
本节的以下主题描述了外键约束的使用:
外键约束命名遵循以下规则:
如果已定义,则使用该
CONSTRAINT
symbol
值。如果
CONSTRAINT
symbol
子句未定义,或CONSTRAINT
关键字后未包含符号:对于
InnoDB
表,约束名称是自动生成的。对于
NDB
表,FOREIGN KEY
index_name
如果已定义,则使用该值。否则,将自动生成约束名称。
该值(如果已定义)在数据库中必须是唯一的。重复 导致错误类似于:ERROR 1005 (HY000): Can't create table 'test.fk1' (errno: 121)。
CONSTRAINT
symbol
symbol
子句中的表和列标识符FOREIGN KEY ...
REFERENCES
可以用反引号 ( `
) 括起来。或者,如果启用了 SQL 模式,则"
可以使用
双引号 ( )。ANSI_QUOTES
系统
lower_case_table_names
变量设置也被考虑在内。
外键约束受以下条件和限制:
父表和子表必须使用相同的存储引擎,不能定义为临时表。
从 5.6.22 开始,创建外键约束至少需要对父表 的
SELECT
、INSERT
、UPDATE
、DELETE
或 特权之一。REFERENCES
外键和引用键中的对应列必须具有相似的数据类型。和等 固定精度类型的大小和符号 必须相同
INTEGER
DECIMAL
。字符串类型的长度不必相同。对于非二进制(字符)字符串列,字符集和排序规则必须相同。MySQL 支持表中一列与另一列之间的外键引用。(列不能有对其自身的外键引用。)在这些情况下, “子表记录”指的是同一表中的从属记录。
MySQL 需要在外键和引用键上建立索引,这样外键检查就可以很快,而不需要表扫描。在引用表中,必须有一个索引,其中外键列以相同的顺序列为 第一列。如果引用表不存在,则会自动在引用表上创建此类索引。如果您创建另一个可用于强制外键约束的索引,则稍后可能会静默删除该索引。
index_name
,如果给定,则如前所述使用。InnoDB
允许外键引用任何索引列或列组。但是,在被引用的表中,必须有一个索引,其中被引用的列是相同顺序的第一 列。InnoDB
添加到索引的隐藏列 也被考虑在内(请参阅第 14.6.2.1 节,“聚簇索引和二级索引”)。NDB
需要在任何引用为外键的列上有一个显式唯一键(或主键)。InnoDB
没有,这是标准 SQL 的扩展。InnoDB
目前不支持具有用户定义分区的表的外键。这包括父表和子表。此限制不适用于 按or
NDB
分区的表(存储引擎支持的唯一用户分区类型);这些可能具有外键引用或者是此类引用的目标。KEY
LINEAR KEY
NDB
不能更改外键关系中的表以使用其他存储引擎。要更改存储引擎,您必须先删除任何外键约束。
有关外键约束的 MySQL 实现与 SQL 标准有何不同的信息,请参阅 第 1.7.2.3 节,“外键约束差异”。
当UPDATE
or
DELETE
操作影响子表中具有匹配行的父表中的键值时,结果取决于子句
和子句
指定的引用操作。参考行动包括:
ON UPDATE
ON DELETE
FOREIGN KEY
CASCADE
:删除或更新父表中的行,并自动删除或更新子表中匹配的行。ON DELETE CASCADE
和均受ON UPDATE CASCADE
支持。两个表之间,不要定义多个ON UPDATE CASCADE
作用于父表或子表中同一列的子句。笔记级联的外键操作不会激活触发器。
SET NULL
:删除或更新父表中的行,并将子表中的外键列设置为NULL
。ON DELETE SET NULL
和ON UPDATE SET NULL
子句均受支持 。如果您指定一个
SET NULL
操作, 请确保您没有将子表中的列声明为NOT NULL
。RESTRICT
:拒绝对父表的删除或更新操作。指定RESTRICT
(orNO ACTION
) 与省略ON DELETE
orON UPDATE
子句相同。NO ACTION
: 来自标准 SQL 的关键字。在 MySQL 中,相当于RESTRICT
. 如果被引用表中存在相关的外键值,MySQL Server 将拒绝对父表的删除或更新操作。有些数据库系统有延期检查,而且NO ACTION
是延期检查。在 MySQL 中,立即检查外键约束,因此NO ACTION
与RESTRICT
.SET DEFAULT
:此操作可被 MySQL 解析器识别,但同时InnoDB
拒绝NDB
包含ON DELETE SET DEFAULT
orON UPDATE SET DEFAULT
子句的表定义。
对于支持外键的存储引擎,如果父表中没有匹配的候选键值,MySQL 将拒绝任何试图在子表中创建外键值的操作
INSERT
。
UPDATE
对于未指定的ON DELETE
or ON
UPDATE
,默认操作始终为RESTRICT
。
对于NDB
表,ON
UPDATE CASCADE
不支持引用父表的主键。
从 NDB 7.3.25 和 NDB 7.4.24 开始:对于
NDB
表,ON DELETE
CASCADE
不支持子表包含任何类型的一个或多个列的
TEXT
情况
BLOB
。(漏洞 #89511,漏洞 #27484882)
InnoDB
对外键约束对应的索引的记录使用深度优先搜索算法进行级联操作。
这个简单的例子通过单列外键
关联parent
和
表:child
CREATE TABLE parent (
id INT NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE child (
id INT,
parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id)
REFERENCES parent(id)
ON DELETE CASCADE
) ENGINE=INNODB;
这是一个更复杂的示例,其中一个
product_order
表具有其他两个表的外键。一个外键引用表中的两列索引product
。另一个引用表中的单列索引
customer
:
CREATE TABLE product (
category INT NOT NULL, id INT NOT NULL,
price DECIMAL,
PRIMARY KEY(category, id)
) ENGINE=INNODB;
CREATE TABLE customer (
id INT NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE product_order (
no INT NOT NULL AUTO_INCREMENT,
product_category INT NOT NULL,
product_id INT NOT NULL,
customer_id INT NOT NULL,
PRIMARY KEY(no),
INDEX (product_category, product_id),
INDEX (customer_id),
FOREIGN KEY (product_category, product_id)
REFERENCES product(category, id)
ON UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY (customer_id)
REFERENCES customer(id)
) ENGINE=INNODB;
ALTER TABLE
您可以使用以下语法
向现有表添加外键约束:
ALTER TABLE tbl_name
ADD [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (col_name, ...)
REFERENCES tbl_name (col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]
外键可以是自引用的(引用同一个表)。当您使用 向表添加外键约束时
ALTER TABLE
,请记住首先在外键引用的列上创建索引。
您可以使用以下
ALTER TABLE
语法删除外键约束:
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;
如果在创建约束时该FOREIGN KEY
子句定义了
CONSTRAINT
名称,则可以引用该名称来删除外键约束。否则,会在内部生成约束名称,您必须使用该值。要确定外键约束名称,请使用SHOW
CREATE TABLE
:
mysql> SHOW CREATE TABLE child\G
*************************** 1. row ***************************
Table: child
Create Table: CREATE TABLE `child` (
`id` int(11) DEFAULT NULL,
`parent_id` int(11) DEFAULT NULL,
KEY `par_ind` (`parent_id`),
CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`)
REFERENCES `parent` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
mysql> ALTER TABLE child DROP FOREIGN KEY `child_ibfk_1`;
ALTER TABLE
支持
在同一语句中添加和删除外键
ALTER TABLE ...
ALGORITHM=INPLACE
。不支持它
ALTER TABLE ...
ALGORITHM=COPY
。
在 MySQL 中,InnoDB 和 NDB 表支持检查外键约束。外键检查由
foreign_key_checks
变量控制,默认情况下启用。通常,您在正常操作期间启用此变量以强制执行参照完整性。从 MySQL NDB Cluster 7.3.2 开始,该foreign_key_checks
变量对表的影响与对
NDB
表
的影响相同InnoDB
。以前,NDB 表的设置被忽略,所有此类检查都被强制执行(错误 #14095855)。
该foreign_key_checks
变量是动态的,支持全局和会话范围。有关使用系统变量的信息,请参阅
第 5.1.8 节,“使用系统变量”。
在以下情况下禁用外键检查很有用:
删除由外键约束引用的表。引用表只能在
foreign_key_checks
禁用后删除。删除表时,表上定义的约束也会被删除。以不同于外键关系所需的顺序重新加载表。例如, mysqldump在转储文件中生成正确的表定义,包括子表的外键约束。为了更容易为具有外键关系的表重新加载转储文件, mysqldump自动在转储输出中包含一条禁用
foreign_key_checks
. 这使您能够以任何顺序导入表,以防转储文件包含外键排序不正确的表。停用foreign_key_checks
还通过避免外键检查来加快导入操作。执行
LOAD DATA
操作,以避免外键检查。ALTER TABLE
对具有外键关系的表 执行操作。
禁用时foreign_key_checks
,将忽略外键约束,但以下情况除外:
如果表定义不符合引用该表的外键约束,则重新创建之前删除的表会返回错误。该表必须具有正确的列名和类型。它还必须在引用的键上有索引。如果不满足这些要求,MySQL 将返回 Error 1005,错误消息中引用 errno:150,这意味着没有正确形成外键约束。
如果更改表的外键定义格式不正确,则更改表会返回错误 (errno: 150)。
删除外键约束所需的索引。在删除索引之前必须删除外键约束。
创建外键约束,其中列引用不匹配的列类型。
禁用foreign_key_checks
有这些额外的影响:
允许删除包含具有外键的表的数据库,这些外键被数据库外部的表引用。
允许删除具有被其他表引用的外键的表。
启用
foreign_key_checks
不会触发表数据扫描,这意味着在禁用时添加到表中的行在 重新启用foreign_key_checks
时不会检查一致性 。foreign_key_checks
要查看外键定义,请使用
SHOW CREATE TABLE
:
mysql> SHOW CREATE TABLE child\G
*************************** 1. row ***************************
Table: child
Create Table: CREATE TABLE `child` (
`id` int(11) DEFAULT NULL,
`parent_id` int(11) DEFAULT NULL,
KEY `par_ind` (`parent_id`),
CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`)
REFERENCES `parent` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
您可以从表
中获取有关外键的信息
。此处显示了针对此表的查询示例:
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_SCHEMA IS NOT NULL;
+--------------+------------+-------------+-----------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME |
+--------------+------------+-------------+-----------------+
| test | child | parent_id | child_ibfk_1 |
+--------------+------------+-------------+-----------------+
您可以从和
表
中获取特定于
InnoDB
外键的
信息。此处显示示例查询:
INNODB_SYS_FOREIGN
INNODB_SYS_FOREIGN_COLS
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN \G
*************************** 1. row ***************************
ID: test/child_ibfk_1
FOR_NAME: test/child
REF_NAME: test/parent
N_COLS: 1
TYPE: 1
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS \G
*************************** 1. row ***************************
ID: test/child_ibfk_1
FOR_COL_NAME: parent_id
REF_COL_NAME: id
POS: 0
当涉及到
InnoDB
表的外键错误时(MySQL Server一般是Error 150),可以通过查看
SHOW ENGINE
INNODB STATUS
输出获取最新的外键错误信息。
mysql> SHOW ENGINE INNODB STATUS\G
...
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2014-10-17 09:51:31 7f47bcde6700 Transaction:
TRANSACTION 436786, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 3
MySQL thread id 1, OS thread handle 0x7f47bcde6700, query id 96 localhost
root update
INSERT INTO child VALUES
(NULL, 1)
, (NULL, 2)
, (NULL, 3)
, (NULL, 4)
, (NULL, 5)
, (NULL, 6)
Foreign key constraint fails for table `mysql`.`child`:
,
CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent`
(`id`)
ON DELETE CASCADE ON UPDATE CASCADE
Trying to add in child table, in index `par_ind` tuple:
DATA TUPLE: 2 fields;
0: len 4; hex 80000003; asc ;;
1: len 4; hex 80000003; asc ;;
But in parent table `mysql`.`parent`, in index `PRIMARY`,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000004; asc ;;
1: len 6; hex 00000006aa26; asc &;;
2: len 7; hex 9d000001610137; asc a 7;;
------------------------
LATEST DETECTED DEADLOCK
------------------------
2014-10-17 09:52:38 7f47bcde6700
*** (1) TRANSACTION:
TRANSACTION 436801, ACTIVE 12 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 2, OS thread handle 0x7f47bcda5700, query id 102 localhost
root updating
DELETE FROM t WHERE i = 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3693 page no 3 n bits 72 index `GEN_CLUST_INDEX` of
table `mysql`.`t` trx id 436801 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info
bits 0
0: len 6; hex 000000003a00; asc : ;;
1: len 6; hex 00000006aa3f; asc ?;;
2: len 7; hex ad0000021d0110; asc ;;
3: len 4; hex 80000001; asc ;;
...
ER_NO_REFERENCED_ROW_2
外键操作的
ER_ROW_IS_REFERENCED_2
错误消息会公开有关父表的信息,即使用户没有父表访问权限也是如此。要隐藏有关父表的信息,请在应用程序代码和存储程序中包含适当的条件处理程序。