13.1.18.5 外键约束

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

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

在CREATE TABLE或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

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

• Identifiers

• 条件与限制

• Referential Actions

• 外键约束示例

• 添加外键约束

• 放弃外键约束

• 外键检查

• 外键定义和元数据

• 外键错误

Identifiers

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

• 如果已定义，则使用CONSTRAINT * symbol *值。

• 如果未定义CONSTRAINT * symbol *子句，或CONSTRAINT关键字后未包含符号：

• 对于InnoDBtable，将自动生成约束名称。

  • 对于NDBtable，如果已定义，则使用FOREIGN KEY * index_name *值。否则，将自动生成约束名称。

• CONSTRAINT symbol值(如果已定义)在数据库中必须唯一。重复的* symbol *导致类似于以下的错误：错误 1005(HY000)：无法创建 table'test.fk1'(errno：121)。

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

条件和限制

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

• 父 table 和子 table 必须使用相同的存储引擎，并且不能将它们定义为临时 table。

• 创建外键约束需要父 table 具有REFERENCES特权。

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

• MySQL 支持 table 中一列与另一列之间的外键引用。 (列本身不能有外键引用.)在这些情况下，“子 table 记录”是指同一 table 中的从属记录。

• MySQL 要求在外键和引用键上构建索引，以便外键检查可以快速进行，而无需进行 table 扫描。在引用 table 中，必须有一个索引，其中外键列以相同的 Sequences 列为* first *列。如果这样的索引不存在，则会在引用 table 上自动创建。如果您创建另一个可用于强制执行外键约束的索引，则以后可能会静默删除该索引。 * index_name *(如果已指定)的用法如前所述。

• InnoDB允许外键引用任何索引列或列组。但是，在引用的 table 中，必须有一个索引，其中引用的列是相同 Sequences 的* first *列。还将考虑InnoDB添加到索引中的隐藏列(请参见第 14.6.2.1 节“群集索引和二级索引”)。

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

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

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

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

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

• 外键约束不能引用虚拟生成的列。

• 在 5.7.16 之前，外键约束不能引用在虚拟生成的列上定义的二级索引。

有关 MySQL 外键约束实现与 SQL 标准如何不同的信息，请参见第 1.8.2.3 节“外键约束差异”。

Referential Actions

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

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

如果以外键关系在两个 table 上都定义了FOREIGN KEY子句，并且将两个 table 都设为父 table 和子 table，则必须为另一个FOREIGN KEY子句定义一个ON UPDATE CASCADE或ON DELETE CASCADE子句，以便级联操作成功。如果仅为一个FOREIGN KEY子句定义了ON UPDATE CASCADE或ON DELETE CASCADE子句，则级联操作将失败并显示错误。

• SET NULL：从父 table 中删除或更新该行，并将子 table 中的一个或多个外键列设置为NULL。 ON DELETE SET NULL和ON UPDATE SET NULL子句均受支持。

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

• RESTRICT：拒绝父 table 的删除或更新操作。指定RESTRICT(或NO ACTION)与省略ON DELETE或ON UPDATE子句相同。

• NO ACTION：来自标准 SQL 的关键字。在 MySQL 中，等效于RESTRICT。如果引用 table 中有相关的外键值，则 MySQL 服务器会拒绝父 table 的删除或更新操作。某些数据库系统具有延迟检查，而NO ACTION是延迟检查。在 MySQL 中，立即检查外键约束，因此NO ACTION与RESTRICT相同。

• SET DEFAULT：MySQL 解析器可以识别此操作，但是InnoDB和NDB都拒绝包含ON DELETE SET DEFAULT或ON UPDATE SET DEFAULT子句的 table 定义。

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

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

对于NDB个 table，如果引用是指向父 table 的主键的，则不支持ON UPDATE CASCADE。

从 NDB 7.5.14 和 NDB 7.6.10 开始：对于NDBtable，如果子 table 包含一个或多个TEXT或BLOB类型的列，则不支持ON DELETE CASCADE。 (缺陷#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引用动作。

在 MySQL 5.7.13 和更早版本中，InnoDB不允许在索引的虚拟生成列的base column上使用级联引用动作定义外键约束。在 MySQL 5.7.14 中取消了此限制。

在 MySQL 5.7.13 和更早版本中，InnoDB不允许在显式包含在virtual index中的非虚拟外键列上定义级联引用动作。在 MySQL 5.7.14 中取消了此限制。

外键约束示例

这个简单的示例通过单列外键关联parent和childtable：

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_ordertable 具有其他两个 table 的外键。一个外键引用producttable 中的两列索引。另一个引用customertable 中的单列索引：

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语法将外键约束添加到现有 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]

外键可以是自引用的(请参见同一 table)。当您使用ALTER TABLE将外键约束添加到 table 时，请记住首先在外键引用的列上创建索引.

删除外键约束

您可以使用以下ALTER TABLE语法删除外键约束：

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

如果在创建约束时FOREIGN KEY子句定义了CONSTRAINT名称，则可以引用该名称以删除外键约束。否则，将在内部生成约束名称，您必须使用该值。要确定外键约束名称，请使用显示创建 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 ... ALGORITHM =插入支持在同一ALTER TABLE语句中添加和删除外键。 ALTER TABLE ... ALGORITHM =复制不支持。

外键检查

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

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

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

• 删除由外键约束引用的 table。只有禁用foreign_key_checks后，才能删除引用的 table。删除 table 时，table 上定义的约束也将被删除。

• 以与外键关系不同的 Sequences 重新加载 table。例如，mysqldump在转储文件中生成 table 的正确定义，包括子 table 的外键约束。为了更轻松地为具有外键关系的 table 重新加载转储文件，mysqldump自动在转储输出中包含一个禁用foreign_key_checks的语句。这使您能够以任何 Sequences 导入 table，以防转储文件包含对外键排序不正确的 table。禁用foreign_key_checks还可以避免外键检查，从而加快了导入操作。

• 执行LOAD DATA操作，以避免外键检查。

• 在具有外键关系的 table 上执行ALTER TABLE操作。

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

• 如果 table 定义不符合引用该 table 的外键约束，则重新创建先前删除的 table 将返回错误。该 table 必须具有正确的列名和类型。它还必须在引用的键上具有索引。如果不满足这些要求，MySQL 将在错误消息中返回错误 1005，该错误指向 errno：150，这意味着未正确形成外键约束。

• 如果错误地为更改后的 table 形成了外键定义，则更改 table 将返回错误(错误号：150)。

• 删除外键约束所需的索引。在删除索引之前，必须删除外键约束。

• 在列引用不匹配的列类型的地方创建外键约束。

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

• 允许删除一个数据库，该数据库包含带有外键的 table，该 table 由数据库外部的 table 引用。

• 允许删除带有其他 table 引用的外键的 table。

• 启用foreign_key_checks不会触发对 table 数据的扫描，这意味着在重新启用foreign_key_checks时不会检查在禁用foreign_key_checks时添加到 table 中的行的一致性。

外键定义和元数据

要查看外键定义，请使用显示创建 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_USAGEtable 中获取有关外键的信息。此处显示了针对该 table 的查询示例：

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_SYS_FOREIGN和INNODB_SYS_FOREIGN_COLStable 中获取特定于InnoDB外键的信息。查询示例如下所示：

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个 table(通常是 MySQL Server 中的错误 150)，则可以通过检查显示引擎的 INNODB 状态输出来获取有关最新外键错误的信息。

mysql> SHOW ENGINE INNODB STATUS\G
...
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2014-10-16 18:35:18 0x7fc2a95c1700 Transaction:
TRANSACTION 1814, 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 2, OS thread handle 140474041767680, query id 74 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 00000000070a; asc       ;;
 2: len 7; hex aa0000011d0134; asc       4;;
...