Documentation Home

13.1.17.5 外键约束

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 symbolsymbol

子句中的表和列标识符FOREIGN KEY ... REFERENCES可以用反引号 ( `) 括起来。或者,如果启用了 SQL 模式,则"可以使用 双引号 ( )。ANSI_QUOTES系统 lower_case_table_names变量设置也被考虑在内。

条件和限制

外键约束受以下条件和限制:

  • 父表和子表必须使用相同的存储引擎,不能定义为临时表。

  • 从 5.6.22 开始,创建外键约束至少需要对父表 的SELECTINSERTUPDATEDELETE或 特权之一。REFERENCES

  • 外键和引用键中的对应列必须具有相似的数据类型。和等 固定精度类型的大小和符号 必须相同INTEGERDECIMAL。字符串类型的长度不必相同。对于非二进制(字符)字符串列,字符集和排序规则必须相同。

  • MySQL 支持表中一列与另一列之间的外键引用。(列不能有对其自身的外键引用。)在这些情况下, 子表记录指的是同一表中的从属记录。

  • MySQL 需要在外键和引用键上建立索引,这样外键检查就可以很快,而不需要表扫描。在引用表中,必须有一个索引,其中外键列以相同的顺序列为 第一列。如果引用表不存在,则会自动在引用表上创建此类索引。如果您创建另一个可用于强制外键约束的索引,则稍后可能会静默删除该索引。 index_name,如果给定,则如前所述使用。

  • InnoDB允许外键引用任何索引列或列组。但是,在被引用的表中,必须有一个索引,其中被引用的列是相同顺序的第一 列。InnoDB添加到索引的隐藏列 也被考虑在内(请参阅第 14.6.2.1 节,“聚簇索引和二级索引”)。

    NDB需要在任何引用为外键的列上有一个显式唯一键(或主键)。 InnoDB没有,这是标准 SQL 的扩展。

  • 不支持外键列的索引前缀。因此,BLOBand TEXT列不能包含在外键中,因为这些列上的索引必须始终包含前缀长度。

  • InnoDB目前不支持具有用户定义分区的表的外键。这包括父表和子表。

    此限制不适用于 按orNDB分区的表(存储引擎支持的唯一用户分区类型);这些可能具有外键引用或者是此类引用的目标。 KEYLINEAR KEYNDB

  • 不能更改外键关系中的表以使用其他存储引擎。要更改存储引擎,您必须先删除任何外键约束。

有关外键约束的 MySQL 实现与 SQL 标准有何不同的信息,请参阅 第 1.7.2.3 节,“外键约束差异”

参考行动

UPDATEor DELETE操作影响子表中具有匹配行的父表中的键值时,结果取决于子句 和子句 指定的引用操作。参考行动包括: ON UPDATEON DELETEFOREIGN KEY

  • CASCADE:删除或更新父表中的行,并自动删除或更新子表中匹配的行。ON DELETE CASCADE和均受ON UPDATE CASCADE 支持。两个表之间,不要定义多个 ON UPDATE CASCADE作用于父表或子表中同一列的子句。

    笔记

    级联的外键操作不会激活触发器。

  • SET NULL:删除或更新父表中的行,并将子表中的外键列设置为NULLON DELETE SET NULLON UPDATE SET NULL子句均受支持 。

    如果您指定一个SET NULL操作, 请确保您没有将子表中的列声明为NOT NULL

  • RESTRICT:拒绝对父表的删除或更新操作。指定 RESTRICT(or NO ACTION) 与省略ON DELETEorON UPDATE子句相同。

  • NO ACTION: 来自标准 SQL 的关键字。在 MySQL 中,相当于RESTRICT. 如果被引用表中存在相关的外键值,MySQL Server 将拒绝对父表的删除或更新操作。有些数据库系统有延期检查,而且NO ACTION是延期检查。在 MySQL 中,立即检查外键约束,因此NO ACTIONRESTRICT.

  • SET DEFAULT:此操作可被 MySQL 解析器识别,但同时 InnoDB拒绝 NDB包含ON DELETE SET DEFAULTor ON UPDATE SET DEFAULT子句的表定义。

对于支持外键的存储引擎,如果父表中没有匹配的候选键值,MySQL 将拒绝任何试图在子表中创建外键值的操作 INSERTUPDATE

对于未指定的ON DELETEor 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_FOREIGNINNODB_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错误消息会公开有关父表的信息,即使用户没有父表访问权限也是如此。要隐藏有关父表的信息,请在应用程序代码和存储程序中包含适当的条件处理程序。