Foreign key constraint naming is governed by the following rules:外键约束命名受以下规则管辖：

Table and column identifiers in a FOREIGN KEY ... REFERENCES clause can be quoted within backticks (`). FOREIGN KEY ... REFERENCES子句中的表和列标识符可以在反点符号（`）中引用。Alternatively, double quotation marks (") can be used if the ANSI_QUOTES SQL mode is enabled. 或者，如果启用了ANSI_QUOTES SQL模式，则可以使用双引号（"）。The lower_case_table_names system variable setting is also taken into account.还考虑了lower_case_table_names系统变量设置。

Foreign key constraints are subject to the following conditions and restrictions:外键约束受以下条件和限制的约束：

For information about how the MySQL implementation of foreign key constraints differs from the SQL standard, see Section 1.7.2.3, “FOREIGN KEY Constraint Differences”.有关外键约束的MySQL实现如何不同于SQL标准的信息，请参阅第1.7.2.3节，“外键约束差异”。

When an UPDATE or DELETE operation affects a key value in the parent table that has matching rows in the child table, the result depends on the referential action specified by ON UPDATE and ON DELETE subclauses of the FOREIGN KEY clause. 当UPDATE操作或DELETE操作影响父表中具有子表中匹配行的键值时，结果取决于FOREIGN KEY子句的ON UPDATE和ON DELETE子类指定的引用操作。Referential actions include:引用操作包括：

For storage engines that support foreign keys, MySQL rejects any INSERT or UPDATE operation that attempts to create a foreign key value in a child table if there is no matching candidate key value in the parent table.对于支持外键的存储引擎，如果父表中没有匹配的候选键值，MySQL将拒绝尝试在子表中创建外键值的任何INSERT或UPDATE操作。

For an ON DELETE or ON UPDATE that is not specified, the default action is always NO ACTION.对于未指定的ON DELETE或ON UPDATE，默认操作始终为NO ACTION。

As the default, an ON DELETE NO ACTION or ON UPDATE NO ACTION clause that is specified explicitly does not appear in SHOW CREATE TABLE output or in tables dumped with mysqldump. 默认情况下，显式指定的ON DELETE NO ACTION或ON UPDATE NO ACTION子句不会出现在SHOW CREATE TABLE输出中或使用mysqldump转储的表中。RESTRICT, which is an equivalent non-default keyword, appears in SHOW CREATE TABLE output and in tables dumped with mysqldump.RESTRICT是一个等效的非默认关键字，出现在SHOW CREATE TABLE输出中和使用mysqldump转储的表中。

For NDB tables, ON UPDATE CASCADE is not supported where the reference is to the parent table's primary key.对于NDB表，如果引用是父表的主键，则不支持ON UPDATE CASCADE。

As of NDB 8.0.16: For NDB tables, ON DELETE CASCADE is not supported where the child table contains one or more columns of any of the TEXT or BLOB types. 从NDB 8.0.16开始：对于NDB表，如果子表包含任何TEXT或BLOB类型的一列或多列，则不支持ON DELETE CASCADE。(Bug #89511, Bug #27484882)

InnoDB performs cascading operations using a depth-first search algorithm on the records of the index that corresponds to the foreign key constraint.InnoDB使用深度优先搜索算法对与外键约束对应的索引记录执行级联操作。

A foreign key constraint on a stored generated column cannot use CASCADE, SET NULL, or SET DEFAULT as ON UPDATE referential actions, nor can it use SET NULL or SET DEFAULT as ON DELETE referential actions.存储的生成列上的外键约束不能在更新引用操作上使用CASCADE、SET NULL或SET DEFAULT，也不能在删除引用操作上使用SET NULL或SET DEFAULT。

A foreign key constraint on the base column of a stored generated column cannot use CASCADE, SET NULL, or SET DEFAULT as ON UPDATE or ON DELETE referential actions.存储的生成列的基列上的外键约束不能在ON UPDATE或ON DELETE引用操作时使用CASCADE、SET NULL或SET DEFAULT。

This simple example relates parent and child tables through a single-column foreign key:此简单示例通过单列外键关联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;

This is a more complex example in which a product_order table has foreign keys for two other tables. 这是一个更复杂的示例，其中一个product_order表具有另外两个表的外键。One foreign key references a two-column index in the product table. 一个外键引用product表中的两列索引。The other references a single-column index in the customer table:另一个引用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;

You can add a foreign key constraint to an existing table using the following ALTER TABLE syntax:可以使用以下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]

The foreign key can be self referential (referring to the same table). 外键可以是自引用的（引用同一个表）。When you add a foreign key constraint to a table using ALTER TABLE, remember to first create an index on the column(s) referenced by the foreign key.使用ALTER TABLE向表中添加外键约束时，请记住首先在外键引用的列上创建索引。

You can drop a foreign key constraint using the following ALTER TABLE syntax:可以使用以下ALTER TABLE语法删除外键约束：

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

If the FOREIGN KEY clause defined a CONSTRAINT name when you created the constraint, you can refer to that name to drop the foreign key constraint. 如果在创建约束时FOREIGN KEY子句定义了CONSTRAINT名称，则可以引用该名称来删除外键约束。Otherwise, a constraint name was generated internally, and you must use that value. 否则，将在内部生成约束名称，您必须使用该值。To determine the foreign key constraint name, use SHOW CREATE TABLE:要确定外键约束名称，请使用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`;

Adding and dropping a foreign key in the same ALTER TABLE statement is supported for ALTER TABLE ... ALGORITHM=INPLACE. ALTER TABLE ... ALGORITHM=INPLACE支持在同一ALTER TABLE语句中添加和删除外键。It is not supported for ALTER TABLE ... ALGORITHM=COPY.ALTER TABLE ... ALGORITHM=COPY不支持它。

Foreign key checking is controlled by the foreign_key_checks variable, which is enabled by default. 外键检查由默认启用的foreign_key_checks变量控制。Typically, you leave this variable enabled during normal operation to enforce referential integrity. 通常，您会在正常操作期间启用此变量以强制引用完整性。The foreign_key_checks variable has the same effect on NDB tables as it does for InnoDB tables.foreign_key_checks变量对NDB表的作用与对InnoDB表的作用相同。

The foreign_key_checks variable is dynamic and supports both global and session scopes. foreign_key_checks变量是动态的，同时支持全局范围和会话范围。For information about using system variables, see Section 5.1.9, “Using System Variables”.有关使用系统变量的信息，请参阅第5.1.9节，“使用系统变量”。

Disabling foreign key checking is useful when:在以下情况下禁用外键检查很有用：

When foreign_key_checks is disabled, foreign key constraints are ignored, with the following exceptions:禁用foreign_key_checks时，将忽略外键约束，但以下情况除外：

Disabling foreign_key_checks has these additional implications:禁用foreign_key_checks会产生以下额外影响：

MySQL extends metadata locks, as necessary, to tables that are related by a foreign key constraint. MySQL根据需要将元数据锁扩展到与外键约束相关的表。Extending metadata locks prevents conflicting DML and DDL operations from executing concurrently on related tables. 扩展元数据锁可防止冲突的DML和DDL操作在相关表上并发执行。This feature also enables updates to foreign key metadata when a parent table is modified. 此功能还允许在修改父表时更新外键元数据。In earlier MySQL releases, foreign key metadata, which is owned by the child table, could not be updated safely.在早期的MySQL版本中，子表拥有的外键元数据无法安全更新。

If a table is locked explicitly with LOCK TABLES, any tables related by a foreign key constraint are opened and locked implicitly. 如果表被LOCK TABLES显式锁定，则与外键约束相关的任何表都将被打开并隐式锁定。For foreign key checks, a shared read-only lock (LOCK TABLES READ) is taken on related tables. 对于外键检查，在相关表上使用共享只读锁（LOCK TABLES READ）。For cascading updates, a shared-nothing write lock (LOCK TABLES WRITE) is taken on related tables that are involved in the operation.对于级联更新，对操作中涉及的相关表执行无共享写锁（LOCK TABLES WRITE）。

To view a foreign key definition, use SHOW CREATE TABLE:要查看外键定义，请使用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

You can obtain information about foreign keys from the INFORMATION_SCHEMA.KEY_COLUMN_USAGE table. 您可以从INFORMATION_SCHEMA.KEY_COLUMN_USAGE表中获取有关外键的信息。An example of a query against this table is shown here:针对该表的查询示例如下所示：

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    |
+--------------+------------+-------------+-----------------+

You can obtain information specific to InnoDB foreign keys from the INNODB_FOREIGN and INNODB_FOREIGN_COLS tables. 您可以从INNODB_FOREIGN和INNODB_FOREIGN_COLS表中获取特定于InnoDB外键的信息。Example queries are show here:示例查询如下所示：

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

In the event of a foreign key error involving InnoDB tables (usually Error 150 in the MySQL Server), information about the latest foreign key error can be obtained by checking SHOW ENGINE INNODB STATUS output.如果出现涉及InnoDB表的外键错误（MySQL服务器中通常为错误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;;
...