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

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

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

条件和限制

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

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

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

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

Referential Actions

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

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

Note

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

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

对于支持外键的存储引擎，如果父 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 节“使用系统变量”。

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

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

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

外键定义和元数据

要查看外键定义，请使用显示创建 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;;
...

Warning

即使用户没有父 table 访问权限，外键操作的ER_NO_REFERENCED_ROW_2和ER_ROW_IS_REFERENCED_2错误消息也会公开有关父 table 的信息。若要隐藏有关父 table 的信息，请在应用程序代码和存储的程序中包括适当的条件处理程序。

首页