外键约束命名遵循以下规则：

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

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

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

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

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

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

对于NDB表，ON UPDATE CASCADE不支持引用父表的主键。

从 NDB 7.5.14 和 NDB 7.6.10 开始：对于 NDB表，ON DELETE CASCADE不支持子表包含任何类型的一个或多个列的 TEXT情况 BLOB。（漏洞 #89511，漏洞 #27484882）

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

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

存储生成列的基列上的外键约束不能使用CASCADE、 SET NULL、 或SET DEFAULT asON UPDATE或ON DELETE引用操作。

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

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

这个简单的例子通过单列外键 关联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。

In MySQL, InnoDB and NDB tables support checking of foreign key constraints. Foreign key checking is controlled by the foreign_key_checks variable, which is enabled by default. 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.

The foreign_key_checks variable is dynamic and supports both global and session scopes. For information about using system variables, see Section 5.1.8, “Using System Variables”.

Disabling foreign key checking is useful when:

When foreign_key_checks is disabled, foreign key constraints are ignored, with the following exceptions:

Disabling foreign_key_checks has these additional implications:

To view a foreign key definition, use 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

You can obtain information about foreign keys from the INFORMATION_SCHEMA.KEY_COLUMN_USAGE table. 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_SYS_FOREIGN and INNODB_SYS_FOREIGN_COLS tables. Example queries are show here:

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-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;;
...