外键约束

MySQL支持外键（允许跨表交叉引用相关数据）和外键约束，这有助于使相关数据保持一致。

外键关系涉及一个包含初始列值的父表，以及一个具有引用父列值的列值的子表。在子表上定义了外键约束。

在CREATE TABLEor 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

本节以下主题下介绍了外键约束的用法：

• 身份标识
• 条件与限制
• 参照动作
• 外键约束示例
• 添加外键约束
• 放弃外键约束
• 外键检查
• 锁定
• 外键定义和元数据
• 外键错误

身份标识

外键约束命名受以下规则支配：

• CONSTRAINTsymbol如果已定义，则使用该值。

• 如果CONSTRAINTsymbol未定义该子句，或者在CONSTRAINT关键字之后未包含符号，则会自动生成约束名称。

  在MySQL 8.0.16之前，如果CONSTRAINTsymbol未定义该子句，或者在CONSTRAINT关键字之后未包含符号，则存储引擎InnoDB和NDB存储引擎都将使用if定义。在MySQL 8.0.16及更高版本中，会被忽略。FOREIGN_KEY index_nameFOREIGN_KEY index_name

• 该值（如果已定义）在数据库中必须唯一。重复会导致类似以下错误：错误1005（HY000）：无法创建表'test.fk1'（errno：121）。CONSTRAINT symbolsymbol
• NDB群集使用与创建时相同的字母大小写来存储外来名称。在8.0.20之前的版本中，当处理SELECT其他SQL语句时，NDB将此类语句中的外键名称与以区分大小写的方式存储的名称（lower_case_table_names等于0 时）进行比较。在NDB 8.0.20及更高版本中，此值不再对进行此类比较产生任何影响，并且总是在不考虑字母大小写的情况下进行比较。（错误＃30512043）

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

条件与限制

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

• 父表和子表必须使用相同的存储引擎，并且不能将它们定义为临时表。
• 创建外键约束需要REFERENCES父表具有特权。
• 外键和引用键中的对应列必须具有相似的数据类型。整数类型的大小和符号必须相同。字符串类型的长度不必相同。对于非二进制（字符）字符串列，字符集和排序规则必须相同。
• MySQL支持表中一列与另一列之间的外键引用。（列本身不能有外键引用。）在这种情况下，“子表记录”是指同一表中的从属记录。
• MySQL要求在外键和引用键上建立索引，以便外键检查可以快速进行，而无需进行表扫描。在引用表中，必须有一个索引，其中外键列以相同的顺序列为第一列。如果这样的索引不存在，则会在引用表上自动创建。如果您创建另一个可用于强制使用外键约束的索引，则以后可能会静默删除该索引。index_name如果给定，则如前所述使用。

• InnoDB允许外键引用任何索引列或列组。但是，在引用表中，必须有一个索引，其中引用列是相同顺序的第一列。InnoDB还考虑了添加到索引的隐藏列（请参见“聚集索引和二级索引”）。

  NDB在被称为外键的任何列上都需要一个显式唯一键（或主键）。InnoDB否，这是标准SQL的扩展。

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

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

  此限制不适用于NDB由KEY或LINEAR KEY（NDB存储引擎支持的唯一用户分区类型）分区的表；它们可能具有外键引用，也可能是此类引用的目标。

• 具有外键关系的表不能更改为使用其他存储引擎。要更改存储引擎，必须首先删除任何外键约束。
• 外键约束不能引用虚拟生成的列。

有关MySQL外键约束实现与SQL标准的不同之处的信息，请参见“外键约束差异”。

参照动作

当“UPDATE或”DELETE操作影响子表中具有匹配行的父表中的键值时，结果取决于该子句的和子句指定的引用动作。参照动作包括：ON UPDATEON DELETEFOREIGN KEY

• CASCADE：从父表中删除或更新该行，并自动删除或更新子表中的匹配行。这两个ON DELETE CASCADE和ON UPDATE CASCADE支持。在两个表之间，不要定义ON UPDATE CASCADE作用于父表或子表中同一列上的多个子句。

  如果一个FOREIGN KEY子句是一个外键关系这两个表定义，使得这两个表父子，一个ON UPDATE CASCADE或ON DELETE CASCADE一个定义节FOREIGN KEY子句必须为其他按顺序定义级联操作成功。如果仅为一个子句定义一个ON UPDATE CASCADE或子句，则级联操作将失败并显示错误。ON DELETE CASCADEFOREIGN KEY

  注意

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

• SET NULL：从父表中删除或更新该行，并将子表中的外键列设置为NULL。这两个ON DELETE SET NULL和ON UPDATE SET NULL条款的支持。

  如果指定SET NULL操作，请确保未将子表中的列声明为NOT NULL。

• RESTRICT：拒绝父表的删除或更新操作。指定RESTRICT（或NO ACTION）与省略ON DELETEor ON UPDATE子句相同。
• NO ACTION：标准SQL中的关键字。在MySQL中，等效于RESTRICT。如果引用表中有相关的外键值，则MySQL服务器会拒绝父表的删除或更新操作。一些数据库系统具有延迟检查，并且NO ACTION是延迟检查。在MySQL中，立即检查外键约束，因此NO ACTION与相同RESTRICT。
• SET DEFAULT：MySQL解析器可以识别此操作，但是两者都拒绝，InnoDB并NDB拒绝包含ON DELETE SET DEFAULTor ON UPDATE SET DEFAULT子句的表定义。

对于支持外键的存储引擎，MySQL将拒绝任何INSERT或UPDATE操作，如果有父表中没有匹配的候选键值试图创建一个子表的外键的值。

对于未指定的ON DELETE或ON UPDATE，默认操作始终为NO ACTION。

默认情况下，显式指定的ON DELETE NO ACTIONor ON UPDATE NO ACTION子句不会出现在SHOW CREATE TABLE输出或使用mysqldump转储的表中。RESTRICT，它是等效的非默认关键字，出现在SHOW CREATE TABLE输出和mysqldump转储的表中。

对于NDB表，ON UPDATE CASCADE在引用到父表的主键的情况下不支持。

从NDB 8.0.16开始：对于子NDB表，ON DELETE CASCADE如果子表包含一个或多个TEXTor BLOB类型的列，则不支持该表。（缺陷＃89511，错误＃27484882）

InnoDB使用深度优先搜索算法对与外键约束相对应的索引记录执行级联操作。

在存储生成列外键约束不能使用CASCADE，SET NULL或SET DEFAULT作为ON UPDATE参照动作，也不能使用SET NULL或SET DEFAULT作为ON DELETE参照动作。

在存储生成列的基本列外键约束不能使用CASCADE，SET NULL或SET DEFAULT作为ON UPDATE或ON DELETE引用操作。

外键约束示例

这个简单的示例通过单列外键关联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 DEFAULT NULL,
  `parent_id` int 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=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
mysql> ALTER TABLE child DROP FOREIGN KEY 	`child_ibfk_1	`;

ALTER TABLE支持在同一语句中添加和删除外键ALTER TABLE ... ALGORITHM=INPLACE。不支持ALTER TABLE ... ALGORITHM=COPY。

外键检查

外键检查由foreign_key_checks变量控制，该变量默认情况下处于启用状态。通常，您在正常操作期间将此变量保持启用状态以强制引用完整性。该foreign_key_checks变量对NDB表的作用与对表的作用相同InnoDB。

该foreign_key_checks变量是动态的，并且支持全局作用域和会话作用域。有关使用系统变量的信息，请参见“使用MySQL服务器系统变量”。

在以下情况下，禁用外键检查非常有用：

• 删除由外键约束引用的表。foreign_key_checks禁用后，只能删除引用的表。删除表时，表上定义的约束也将被删除。
• 以与外键关系不同的顺序重新加载表。例如，mysqldump在转储文件中产生表的正确定义，包括子表的外键约束。为了更轻松地为具有外键关系的表重新加载转储文件，mysqldump自动在转储输出中包含一条禁用的语句foreign_key_checks。这使您能够以任何顺序导入表，以防转储文件包含对外键排序不正确的表。禁用foreign_key_checks还避免了外键检查，从而加快了导入操作。
• 执行LOAD DATA操作时，避免外键检查。
• ALTER TABLE在具有外键关系的表上执行操作。

当foreign_key_checks被禁用，外键约束将被忽略，但下列情况除外：

• 如果表定义不符合引用该表的外键约束，则重新创建先前已删除的表将返回错误。该表必须具有正确的列名和类型。它还必须在引用的键上具有索引。如果不满足这些要求，MySQL将在错误消息中返回错误1005，该错误指向errno：150，这意味着未正确形成外键约束。
• 如果错误地为更改后的表形成了外键定义，则更改表将返回错误（错误号：150）。
• 删除外键约束所需的索引。在删除索引之前，必须删除外键约束。
• 在列引用不匹配的列类型的地方创建外键约束。

禁用foreign_key_checks还具有以下其他含义：

• 允许删除包含带有外键的表的数据库，该表由数据库外部的表引用。
• 允许删除带有其他表引用的外键的表。
• 启用foreign_key_checks不会触发对表数据的扫描，这意味着在foreign_key_checks禁用时添加到表中的行在foreign_key_checks重新启用时不会进行一致性检查。

锁定

MySQL根据需要将元数据锁扩展到由外键约束关联的表。扩展元数据锁可防止冲突的DML和DDL操作在相关表上并发执行。修改父表后，此功能还可以更新外键元数据。在早期的MySQL版本中，子表拥有的外键元数据无法安全更新。

如果使用显式锁定了表LOCK TABLES，则任何与外键约束相关的表都将被打开并隐式锁定。对于外键检查，LOCK TABLES READ将在相关表上使用共享的只读锁（）。对于级联更新，LOCK TABLES WRITE将对操作中涉及的相关表采取无共享写锁定（）。

外键定义和元数据

要参见外键定义，请使用SHOW CREATE TABLE：

mysql> SHOW CREATE TABLE child\G
*************************** 1. row 	***************************
       Table	: child
Create Table	: CREATE TABLE `child` (
  `id` int DEFAULT NULL,
  `parent_id` int 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=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

您可以从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_FOREIGN和INNODB_FOREIGN_COLS表获取特定于外键的信息。查询示例在这里显示：

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_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_FOREIGN_COLS \G*************************** 1. row 	***************************
          ID	: test/child_ibfk_1
FOR_COL_NAME	: parent_id
REF_COL_NAME	: id
         POS	: 0

外键错误

如果涉及InnoDB表的外键错误（通常是MySQL Server中的错误150），则可以通过检查SHOW ENGINE INNODB STATUS输出来获取有关最新外键错误的信息。

mysql> SHOW ENGINE INNODB STATUS\G
...
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2018-04-12 14:57:24 0x7f97a9c91700 Transaction:
TRANSACTION 7717, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 3
MySQL thread id 8, OS thread handle 140289365317376, query id 14 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 `test`.`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 `test`.`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 000000001e19; asc       ;;
 2: len 7; hex 81000001110137; asc       7;;
...

警告

If a user has table-level privileges for all parent tables,ER_NO_REFERENCED_ROW_2 and ER_ROW_IS_REFERENCED_2 error messages for foreign key operations expose information about parent tables. If a user does not have table-level privileges for all parent tables, more generic error messages are displayed instead(ER_NO_REFERENCED_ROW and ER_ROW_IS_REFERENCED).

一个例外是，对于定义为具有DEFINER特权执行的存储程序，针对其评估特权的用户是program DEFINER子句中的用户，而不是调用用户。如果该用户具有表级父表特权，则仍显示父表信息。在这种情况下，存储程序创建者有责任通过包括适当的条件处理程序来隐藏信息。