MySQL的SQL语句 - 数据定义语句（14）- CREATE TABLE 语句 (7)

原创

数据杂货铺 2020-12-05 17:02:10 ©著作权

文章标签 JAVA 文章分类 Java 后端开发

©著作权归作者所有：来自51CTO博客作者数据杂货铺的原创作品，请联系作者获取转载授权，否则将追究法律责任

FOREIGN KEY 约束

MySQL 支持外键(允许跨表交叉引用相关数据)和外键约束(帮助保持相关数据的一致性)。

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

在 CREATE TABLE 或 ALTER TABLE 语句中定义外键约束的基本语法包括:

1. [CONSTRAINT [symbol]] FOREIGN KEY
2.     [index_name] (col_name, ...)
3.     REFERENCES tbl_name (col_name,...)
4.     [ON DELETE reference_option]
5.     [ON UPDATE reference_option]
6. 
7. reference_option:
8.     RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

本节将从以下各个主题描述外键约束的使用:

标识符

外键约束命名由以下规则控制:

● 如果定义了，则使用 CONSTRAINT symbol 值。

● 如果没有定义 CONSTRAINT symbol 子句，或者在 CONSTRAINT 关键字之后没有包含 symbol，则自动生成约束名称。

在 MySQL 8.0.16 之前，如果 CONSTRAINT symbol 子句没有定义，或者 CONSTRAINT 关键字后面没有包含符合名称，InnoDB 和 NDB 存储引擎都会使用 FOREIGN_KEY index_name（如果定义了）。在 MySQL 8.0.16 及更高版本中，会忽略 FOREIGN_KEY index_name。

● CONSTRAINT symbol 值(如果定义)在数据库中必须是唯一的。重复的 symbol 会导致类似这样的错误: ERROR 1005 (HY000): Can't create table 'test.fk1' (errno: 121)。

● NDB Cluster 使用与创建时相同字母大小写存储外键名称。在 8.0.20 版本之前，在处理 SELECT 和其他 SQL 语句时，当 lower_case_table_names 等于0时，NDB会将这些语句中的外键名称与以区分大小写的方式存储的名称进行比较。在 NDB 8.0.20 以及以后的版本中，这个值不再对如何进行比较产生影响，并且它们总是不考虑字母大小写。

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

条件和限制

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

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

● 创建外键约束需要父表上的 REFERENCES 权限。

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

● MySQL支持一个表中列和列之间的外键引用。(列不能有对自己的外键引用。)在这些情况下，“子表记录”指的是同一表中的从属记录。

● MySQL需要外键和引用键上的索引，这样外键检查可以很快，不需要表扫描。在引用表中，必须有一个索引，其中外键列按照相同的顺序作为第一列列出。如果索引不存在，则在引用表上自动创建索引。如果您创建了另一个可用于强制外键约束的索引，则此索引可能会在稍后以静默方式删除。如果给定 index_name，则按照前面描述的方式使用。

● InnoDB 允许一个外键引用任何索引列或列组。但是，在引用的表中，必须有一个索引，其中引用的列是按照相同顺序排列的第一列。InnoDB添加到索引中的隐藏列也会算数。

NDB 要求在作为外键引用的任何列上有一个显式的唯一键(或主键)。InnoDB 不是，它是标准SQL的扩展。

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

● InnoDB 目前不支持使用用户定义分区的表的外键。这包括父表和子表。

这个限制不适用于按 KEY 或 LINEAR KEY (NDB 存储引擎支持的唯一用户分区类型) 分区的 NDB 表，它们可能具有外键引用，或者是这些引用的目标。

● 外键关系中的表不能更改为使用另一个存储引擎。要更改存储引擎，必须首先删除任何外键约束。

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

引用行为

当 UPDATE 或 DELETE 操作影响父表中的键值，而此键值在子表中具有匹配行时，结果取决于 FOREIGN KEY 子句的 ON UPDATE 和 ON DELETE 子句指定的引用行为。引用行为包括:

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

如果存在外键关系的两个表上都定义了 FOREIGN KEY 子句，使这两个表都成为父表和子表，那么为一个 FOREIGN KEY 子句定义的 ON UPDATE CASCADE 或 ON DELETE CASCADE 子句必须也为另一个外键子句定义，以便级联操作成功。如果仅为一个 FOREIGN KEY 子句定义了 ON UPDATE CASCADE 或 ON DELETE CASCADE 子句，则级联操作会失败并报错。

注意

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

● SET NULL: 删除或更新父表中的行，并将子表中的外键列或列组设置为 NULL。ON DELETE SET NULL 和 ON UPDATE SET NULL 子句都支持。

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

● RESTRICT: 拒绝对父表的删除或更新操作。指定 RESTRICT(或 NO ACTION)等效于省略 ON DELETE 和 ON UPDATE 子句。

● NO ACTION: 来自标准 SQL 的关键字。在 MySQL 中，相当于 RESTRICT。如果引用表中有相关的外键值，MySQL服务器拒绝对父表执行删除或更新操作。一些数据库系统有延迟检查，NO ACTION 就是延迟检查。在 MySQL 中，外键约束会被立即检查，所以 NO ACTION 与 RESTRICT 相同。

● SET DEFAULT: MySQL 解析器可以识别这个动作，但是 InnoDB 和 NDB 都拒绝包含 ON DELETE SET DEFAULT 或 ON UPDATE SET DEFAULT 子句的表定义。

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

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

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

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

从 NDB 8.0.16 开始，对于 NDB 表，当子表包含一个或多个 TEXT 或 BLOB 类型列时，不支持 ON DELETE CASCADE。

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

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

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

外键约束示例

这个简单的例子通过单列外键将父表和子表关联起来:

1. CREATE TABLE parent (
2.     id INT NOT NULL,
3.     PRIMARY KEY (id)
4. ) ENGINE=INNODB;
5. 
6. CREATE TABLE child (
7.     id INT,
8.     parent_id INT,
9.     INDEX par_ind (parent_id),
10.     FOREIGN KEY (parent_id)
11.         REFERENCES parent(id)
12.         ON DELETE CASCADE
13. ) ENGINE=INNODB;

这是一个更复杂的示例，其中 product_order 表具有另外两个表的外键。一个外键引用 product 表中的两列索引。另一个引用 customer 表中的单列索引:

1. CREATE TABLE product (
2.     category INT NOT NULL, id INT NOT NULL,
3.     price DECIMAL,
4.     PRIMARY KEY(category, id)
5. )   ENGINE=INNODB;
6. 
7. CREATE TABLE customer (
8.     id INT NOT NULL,
9.     PRIMARY KEY (id)
10. )   ENGINE=INNODB;
11. 
12. CREATE TABLE product_order (
13.     no INT NOT NULL AUTO_INCREMENT,
14.     product_category INT NOT NULL,
15.     product_id INT NOT NULL,
16.     customer_id INT NOT NULL,
17. 
18.     PRIMARY KEY(no),
19.     INDEX (product_category, product_id),
20.     INDEX (customer_id),
21. 
22.     FOREIGN KEY (product_category, product_id)
23.       REFERENCES product(category, id)
24.       ON UPDATE CASCADE ON DELETE RESTRICT,
25. 
26.     FOREIGN KEY (customer_id)
27.       REFERENCES customer(id)
28. )   ENGINE=INNODB;

添加外键约束

可以使用以下 ALTER TABLE 语法向现有表添加外键约束:

1. ALTER TABLE tbl_name
2.     ADD [CONSTRAINT [symbol]] FOREIGN KEY
3.     [index_name] (col_name, ...)
4.     REFERENCES tbl_name (col_name,...)
5.     [ON DELETE reference_option]
6.     [ON UPDATE reference_option]

外键可以是自引用的(引用相同的表)。当使用 ALTER TABLE 向表添加外键约束时，请记住首先在外键引用的列上创建索引。

删除外键约束

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

1. ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

如果在创建约束时 FOREIGN KEY 子句定义了一个 CONSTRAINT 名称，则可以引用该名称来删除外键约束。否则，将在内部生成一个约束名称，并且必须使用该值。要确定外键约束名称，请使用 SHOW CREATE TABLE 语句:

1. mysql> SHOW CREATE TABLE child\G
2. *************************** 1. row ***************************
3.        Table: child
4. Create Table: CREATE TABLE `child` (
5.   `id` int DEFAULT NULL,
6.   `parent_id` int DEFAULT NULL,
7.   KEY `par_ind` (`parent_id`),
8.   CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`)
9.   REFERENCES `parent` (`id`) ON DELETE CASCADE
10. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
11. 
12. mysql> ALTER TABLE child DROP FOREIGN KEY `child_ibfk_1`;
. ```
 
在 ALTER TABLE ... ALGORITHM=INPLACE 语句中支持在同一ALTER TABLE语句中添加和删除外键。在 ALTER TABLE ... ALGORITHM=COPY 中不支持这种操作。
 
外键检查
 
外键检查由 foreign_key_checks 变量控制，该变量在默认情况下是启用的。通常，在正常操作期间启用该变量以强制引用完整性。foreign_key_check 变量对 NDB 表和 InnoDB 表的作用是一样的。
 
foreign_key_checks 变量是动态的，并且支持全局和会话作用域。
 
在以下情况禁用外键检查是有用的:
 
● 删除由外键约束引用的表。只能在禁用 foreign_key_checks 之后删除引用表。当您删除一个表时，在该表上定义的约束也会被删除。
 
● 按照与外键关系不同的顺序重新加载表。例如，mysqldump 在转储文件中生成正确的表定义，包括子表的外键约束。为了更容易为具有外键关系的表重新加载转储文件，mysqldump 自动在转储输出中包括一条禁用 foreign_key_checking 的语句。这使您能够以任何顺序导入表，以防止转储文件中包含外键顺序不正确的表。禁用 foreign_key_checks 还可以避免外键检查，从而加速导入操作。
 
● 执行 LOAD DATA 操作，避免外键检查。
 
● 对具有外键关系的表执行 ALTER TABLE 操作。
 
当禁用 foreign_key_checks 时，将忽略外键约束，但有以下例外:
 
● 重新创建先前删除的表，如果表定义不符合引用该表的外键约束，将返回错误。表必须具有正确的列名和类型。它还必须在引用的键上有索引。如果这些要求没有得到满足，MySQL 返回错误 1005，在错误消息中引用 errno: 150，意味着没有遵守外键约束。
 
● 如果修改表的外键定义不正确，那么修改表将返回一个错误(errno: 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 表获取关于外键的信息。这个表的查询示例如下所示:



1. mysql> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
2.        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
3.        WHERE REFERENCED_TABLE_SCHEMA IS NOT NULL;
4. +--------------+------------+-------------+-----------------+
5. | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME |
6. +--------------+------------+-------------+-----------------+
7. | test         | child      | parent_id   | child_ibfk_1    |
8. +--------------+------------+-------------+-----------------+

可以从 INNODB_FOREIGN 和 INNODB_FOREIGN_COLS 表中获取特定于 InnoDB 外键的信息。示例查询:

1. mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN \G
2. *************************** 1. row ***************************
3.       ID: test/child_ibfk_1
4. FOR_NAME: test/child
5. REF_NAME: test/parent
6.   N_COLS: 1
7.     TYPE: 1
8. 
9. mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS \G
10. *************************** 1. row ***************************
11.           ID: test/child_ibfk_1
12. FOR_COL_NAME: parent_id
13. REF_COL_NAME: id
14.          POS: 0

外键错误

在发生涉及InnoDB表的外键错误时(通常是 MySQL 服务器的 Error 150)，关于最新外键错误的信息可以通过检查 SHOW ENGINE INNODB STATUS 语句的输出来获得。

1. mysql> SHOW ENGINE INNODB STATUS\G
2. ...
3. ------------------------
4. LATEST FOREIGN KEY ERROR
5. ------------------------
6. 2018-04-12 14:57:24 0x7f97a9c91700 Transaction:
7. TRANSACTION 7717, ACTIVE 0 sec inserting
8. mysql tables in use 1, locked 1
9. 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 3
10. MySQL thread id 8, OS thread handle 140289365317376, query id 14 localhost root update
11. INSERT INTO child VALUES (NULL, 1), (NULL, 2), (NULL, 3), (NULL, 4), (NULL, 5), (NULL, 6)
12. Foreign key constraint fails for table `test`.`child`:
13. ,
14.   CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE
15.   CASCADE ON UPDATE CASCADE
16. Trying to add in child table, in index par_ind tuple:
17. DATA TUPLE: 2 fields;
18.  0: len 4; hex 80000003; asc     ;;
19.  1: len 4; hex 80000003; asc     ;;
20. 
21. But in parent table `test`.`parent`, in index PRIMARY,
22. the closest match we can find is record:
23. PHYSICAL RECORD: n_fields 3; compact format; info bits 0
24.  0: len 4; hex 80000004; asc     ;;
25.  1: len 6; hex 000000001e19; asc       ;;
26.  2: len 7; hex 81000001110137; asc       7;;
27. ...

警告

如果用户拥有所有父表的表级权限，那么外键操作的 ER_NO_REFERENCED_ROW_2 和 ER_ROW_IS_REFERENCED_2 错误消息将公开有关父表的信息。如果用户没有所有父表的表级权限，则会显示更通用的错误消息(ER_NO_REFERENCED_ROW 和 ER_ROW_IS_REFERENCED)。

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

官方地址： https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html