备案控制台
文档
产品文档
输入文档关键字查找
首页 云原生数据库 PolarDB 云原生数据库PolarDB分布式版 PolarDB-X（2.0） 开发指南 外键

外键

更新时间：
一键部署
产品详情
相关技术圈
我的收藏

本文介绍了外键的使用方法。

重要

由于在分区表上检查和维护外键约束的实现比单机数据库更为复杂，不合理的外键使用可能会导致较大的性能开销，导致系统吞吐显著下降。应用在性能敏感的场景之前，建议您对数据进行充分验证和全面测试后谨慎使用。

创建外键

开关

您可以通过下面的参数控制外键功能的开启和关闭。 

SET [GLOBAL] enable_foreign_key = [true | false]

语法 

[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

命名

外键命名遵循以下规则：

  • 如果在CONSTRAINT中指定了名称，则使用该名称。

  • 如果CONSTRAINT语句未指定名称，会自动生成一个CONSTRAINT名称，并使用该名称。

  • index_name仅作为伴随外键创建的索引的名称，不代表外键名称。

  • 外键名称必须在当前表中唯一。

支持的数据库类型

外键支持AUTO模式和DRDS模式，并支持跨AUTO和DRDS库之间建立外键。

条件和限制

  • 仅支持在InnoDB引擎上创建外键。

  • 外键中的列和引用的父表中的列必须是相同的数据类型，并具有相同的大小、精度、长度、字符集（charset）和排序规则（collation）。

  • 外键中父表与子表的列个数必须一致，并且必须存在。

  • 不支持超过64个字符的外键名、索引名、表名、列名。

  • 外键中的列和引用的父表中的列必须有相同的索引，并且索引中的列顺序必须与外键的列顺序一样，这样才能在执行外键约束检查时使用索引来避免全表扫描。

    如果子表中没有对应的外键索引，则会自动创建一个索引，索引名可以指定或与外键名一致。

  • 不支持前缀索引，因此不支持在BLOB和TEXT类型的列上创建外键。

  • 不允许引用方式为SET NULL的外键的引用列为NOT NULL（主键）。

  • 不支持创建引用方式为SET DEFUALT的外键。

与MySQL不同的限制

不支持在生成列(Stored, Virtual & Logical)上创建外键。

引用操作

当UPDATE或DELETE操作影响父表中的数据，且父表在子表中有相匹配的外键值时，子表的结果取决于外键定义中ON UPDATE和ON DELETE的引用操作，引用操作包括：

参数

用法

ON DELETE NO ACTION/

ON UPDATE NO ACTION

默认参数，在更新或删除父表字段时，如果字段有外键引用，语句会在执行更新或删除字段时失败。

ON DELETE RESTRICT/

ON UPDATE RESTRICT

ON DELETE NO ACTION和ON UPDATE NO ACTION的别名。

ON DELETE CASCADE/

ON UPDATE CASCADE

在更新或删除父表字段时，如果字段有外键引用，则会进行级联更新或删除，即引用该列的所有行将被更新或删除。

ON DELETE SET NULL/

ON UPDATE SET NULL

在更新或删除父表字段时，如果字段有外键引用，则会被置为NULL，如果该列是NOT NULL，则会更新失败。

ON DELETE SET DEFAULT/

ON UPDATE SET DEFAULT

暂不支持。

外键约束示例（AUTO模式）

本文均在AUTO模式下演示外键功能。

下面是一个在parent和child表中通过单列建立的外键： 

CREATE TABLE parent (
    id INT NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE child (
    id INT,
    parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE
);

下面是一个更复杂的示例，product_order表有两个分别引用其他表的外键。一个外键引用product表中的两列索引。另一个引用customer表中的单列索引： 

CREATE TABLE product (
    category INT NOT NULL, id INT NOT NULL,
    price DECIMAL,
    PRIMARY KEY(category, id)
);

CREATE TABLE customer (
    id INT NOT NULL,
    PRIMARY KEY (id)
);

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

增加外键约束

您可以使用以下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;

如果外键约束在创建时定义了名称，则可以引用该名称来删除外键约束。否则，只能引用自动生成的约束名称进行删除。您可以使用SHOW CREATE TABLE查看外键名称： 

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,
	CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
	INDEX `par_ind` (`parent_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4

外键约束检查

PolarDB-X支持外键约束检查。外键检查由foreign_key_checks变量控制，默认情况下开启。在一般的操作中保持该变量开启可以保证外键引用关系的完整性。

foreign_key_checks具有GLOBAL和SESSION两种作用域。

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

  • 当删除一个被外键引用的父表时，只有在关闭外键约束检查时才能删除成功。

  • 当给数据库导入数据时，创建表的顺序可能和外键依赖顺序不一样而导致创建表报错，只有在关闭外键约束检查时才能成功创建表，另外，导入数据时关闭外键约束检查也能加快导数据的速度。

  • 当给数据库导入数据时，先导入子表的数据会报错，只有关闭外键约束检查，才能确保顺利导入子表数据。

  • 执行部分有关外键的ALTER TABLE操作时，关闭外键约束检查才能执行成功。

禁用外键检查后，不会执行外键约束检查以及引用操作，但以下场景除外：

  • 如果修改表结构会导致外键定义不正确，则依然会报错。

  • 删除外键所需的索引时，需要先删除外键，删除外键所需的索引会报错。

  • 创建外键时，如果不符合外键的条件或限制，则依然会报错。

  • 删除跨库外键中父表所在的库时，会出现报错。

禁用外键检查具有以下额外含义：

  • 当一个表中存在引用其他表的外键，并同时被其他表通过外键引用时，它仍然可以被删除。

  • 开启外键检查不会扫描表中的数据，这意味着在检查禁用时添加到表中的数据，并不会随着其重新启用而检查一致性。

锁

在INSERT或者UPDATE子表时，外键约束会检查父表中是否存在对应的外键值，会开启事务，并对父表中的该行数据上锁，避免该外键值被其他操作修改，导致破坏外键约束。这里的上锁行为等同于对父表中外键值所在行做SELECT FOR UPDATE操作。在并发写入子表场景，如果引用的外键值大部分都一样，可能会有比较严重的锁冲突。

外键定义和元数据

要查看外键定义，请使用SHOW CREATE TABLE或SHOW FULL CREATE TABLE（支持查看是逻辑外键或物理外键）。 

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,
	CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
	INDEX `par_ind` (`parent_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4

SHOW FULL CREATE TABLE child\G
*************************** 1. row ***************************
       Table: child
Create Table: CREATE PARTITION TABLE `child` (
	`id` int(11) DEFAULT NULL,
	`parent_id` int(11) DEFAULT NULL,
	`_drds_implicit_id_` bigint(20) NOT NULL AUTO_INCREMENT,
	PRIMARY KEY (`_drds_implicit_id_`),
	CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION /* TYPE LOGICAL */,
	GLOBAL INDEX /* par_ind_$871c */ `par_ind` (`parent_id`)
		PARTITION BY KEY(`parent_id`,`_drds_implicit_id_`)
		PARTITIONS 3,
	LOCAL KEY `_local_par_ind` (`parent_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY(`_drds_implicit_id_`)
PARTITIONS 3
/* tablegroup = `tg1121` */

也可以从以下系统表中获取外键有关信息：

  • INFORMATION_SCHEMA.TABLE_CONSTRAINTS

  • INFORMATION_SCHEMA.KEY_COLUMN_USAGE

  • INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

  • INFORMATION_SCHEMA.INNODB_SYS_FOREIGN

  • INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS 

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = 'test';
+--------------+---------------+------------------+----------------------+
| TABLE_SCHEMA | TABLE_NAME    | COLUMN_NAME      | CONSTRAINT_NAME      |
+--------------+---------------+------------------+----------------------+
| test         | product_order | customer_id      | product_order_ibfk_2 |
| test         | child         | parent_id        | child_ibfk_1         |
| test         | product_order | product_category | product_order_ibfk_1 |
| test         | product_order | product_id       | product_order_ibfk_1 |
+--------------+---------------+------------------+----------------------+

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='FOREIGN KEY' AND CONSTRAINT_SCHEMA = 'test'\G
*************************** 1. row ***************************
CONSTRAINT_CATALOG: def
 CONSTRAINT_SCHEMA: test
   CONSTRAINT_NAME: child_ibfk_1
      TABLE_SCHEMA: test
        TABLE_NAME: child
   CONSTRAINT_TYPE: FOREIGN KEY
          ENFORCED: yes

SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = 'test'\G
*************************** 1. row ***************************
       CONSTRAINT_CATALOG: def
        CONSTRAINT_SCHEMA: test
          CONSTRAINT_NAME: child_ibfk_1
UNIQUE_CONSTRAINT_CATALOG: def
 UNIQUE_CONSTRAINT_SCHEMA: test
   UNIQUE_CONSTRAINT_NAME: PRIMARY
             MATCH_OPTION: NONE
              UPDATE_RULE: NO ACTION
              DELETE_RULE: CASCADE
               TABLE_NAME: child
    REFERENCED_TABLE_NAME: parent
    
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN WHERE ID = 'test/child_ibfk_1';
+-------------------+------------+-------------+--------+------+
| ID                | FOR_NAME   | REF_NAME    | N_COLS | TYPE |
+-------------------+------------+-------------+--------+------+
| test/child_ibfk_1 | test/child | test/parent |      1 |   33 |
+-------------------+------------+-------------+--------+------+

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS WHERE ID = 'test/child_ibfk_1';
+-------------------+--------------+--------------+------+
| ID                | FOR_COL_NAME | REF_COL_NAME | POS  |
+-------------------+--------------+--------------+------+
| test/child_ibfk_1 | parent_id    | id           |    0 |
+-------------------+--------------+--------------+------+

查看外键级联的执行计划

您可以通过EXPLAIN命令查看外键级联中可能涉及的执行计划，Foreign Key提示了需要检查的外键。 

EXPLAIN DELETE FROM parent WHERE id = 1;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN                                                                                                                                                    |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| LogicalModify(TYPE="DELETE", TABLES="test.parent")                                                                                                                       |
|   LogicalView(tables="parent[p3]", sql="SELECT `id` FROM `parent` AS `parent` WHERE (`id` = ?) FOR UPDATE")                                                              |
| >> Foreign Key: test.child.child_ibfk_1                                                                                                                                  |
|   LogicalModify(TYPE="DELETE", TABLES="test.child")                                                                                                                      |
|     Gather(concurrent=true)                                                                                                                                              |
|       LogicalView(tables="child[p1,p2,p3]", shardCount=3, sql="SELECT `id`, `parent_id` FROM `child` AS `child` WHERE (`parent_id` = ?) FOR UPDATE")                     |
| >>>> Foreign Key: test.grand_child.grand_child_ibfk_1                                                                                                                    |
|     LogicalModify(TYPE="DELETE", TABLES="test.grand_child")                                                                                                              |
|       Gather(concurrent=true)                                                                                                                                            |
|         LogicalView(tables="grand_child[p1,p2,p3]", shardCount=3, sql="SELECT `id`, `parent_id` FROM `grand_child` AS `grand_child` WHERE (`parent_id` = ?) FOR UPDATE") |
| HitCache:true                                                                                                                                                            |
| Source:PLAN_CACHE                                                                                                                                                        |
| TemplateId: 78fcce0f                                                                                                                                                     |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

您还可以通过EXPLAIN COST估算代价，以避免复杂的级联操作可能造成的过大的性能开销。

具体可见EXPLAIN语句。 

EXPLAIN COST DELETE FROM parent WHERE id = 1;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN                                                                                                                                                                                                                                                       |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| LogicalModify(TYPE="DELETE", TABLES="test.parent"): rowcount = 1.0, cumulative cost = value = 1.0005002E7, cpu = 2.0, memory = 0.0, io = 1.0, net = 2.0                                                                                                                     |
|   LogicalView(tables="parent[p3]", sql="SELECT `id` FROM `parent` AS `parent` WHERE (`id` = ?) FOR UPDATE"): rowcount = 1.0, cumulative cost = value = 5005001.0, cpu = 1.0, memory = 0.0, io = 1.0, net = 1.0                                                              |
| >> Foreign Key: test.child.child_ibfk_1                                                                                                                                                                                                                                     |
|   LogicalModify(TYPE="DELETE", TABLES="test.child"): rowcount = 1.0, cumulative cost = value = 1.2505003E7, cpu = 3.0, memory = 0.0, io = 1.0, net = 2.5                                                                                                                    |
|     Gather(concurrent=true): rowcount = 1.0, cumulative cost = value = 7505002.0, cpu = 2.0, memory = 0.0, io = 1.0, net = 1.5                                                                                                                                              |
|       LogicalView(tables="child[p1,p2,p3]", shardCount=3, sql="SELECT `id`, `parent_id` FROM `child` AS `child` WHERE (`parent_id` = ?) FOR UPDATE"): rowcount = 1.0, cumulative cost = value = 7505001.0, cpu = 1.0, memory = 0.0, io = 1.0, net = 1.5                     |
| >>>> Foreign Key: test.grand_child.grand_child_ibfk_1                                                                                                                                                                                                                       |
|     LogicalModify(TYPE="DELETE", TABLES="test.grand_child"): rowcount = 1.0, cumulative cost = value = 1.2505003E7, cpu = 3.0, memory = 0.0, io = 1.0, net = 2.5                                                                                                            |
|       Gather(concurrent=true): rowcount = 1.0, cumulative cost = value = 7505002.0, cpu = 2.0, memory = 0.0, io = 1.0, net = 1.5                                                                                                                                            |
|         LogicalView(tables="grand_child[p1,p2,p3]", shardCount=3, sql="SELECT `id`, `parent_id` FROM `grand_child` AS `grand_child` WHERE (`parent_id` = ?) FOR UPDATE"): rowcount = 1.0, cumulative cost = value = 7505001.0, cpu = 1.0, memory = 0.0, io = 1.0, net = 1.5 |
| HitCache:true                                                                                                                                                                                                                                                               |
| Source:PLAN_CACHE                                                                                                                                                                                                                                                           |
| WorkloadType: TP                                                                                                                                                                                                                                                            |
| TemplateId: 78fcce0f                                                                                                                                                                                                                                                        |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

兼容性

外键约束检查与下推

PolarDB-X在创建外键时，会根据表的形态决定是否将外键下推，下推的外键称为物理外键，非下推的外键称为逻辑外键，下推外键会提升性能。

当关闭外键约束检查后，进行删除并重建父表，或首先创建带有外键的子表的操作时，由于无法确认此时父表的形态，会默认创建逻辑外键。

与MySQL的兼容性

匹配方式

MySQL中的外键约束支持三种匹配语法，分为MATCH SIMPLE | MATCH FULL | MATCH PARTIAL，其中默认的匹配方式是MATCH SIMPLE，MATCH PARTIAL尚未实现。PolarDB-X支持默认的MATCH SIMPLE匹配方式，并暂时不支持修改匹配方式。

内联REFERENCES

MySQL和PolarDB-X均能解析但会忽略以内联REFERENCES的方式定义的外键。只有当REFERENCES作为FOREIGN KEY定义的一部分时，才会进行检查和执行。下面的示例在定义外键约束时只使用了REFERENCES： 

CREATE TABLE parent (
    id INT KEY
);

CREATE TABLE child (
    id INT,
    pid INT REFERENCES parent(id)
);

SHOW CREATE TABLE child;

输出结果显示child表不包含任何外键：

SHOW CREATE TABLE child\G
*************************** 1. row ***************************
       Table: child
Create Table: CREATE TABLE `child` (
	`id` int(11) DEFAULT NULL,
	`pid` int(11) DEFAULT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4

文档推荐
  • 本页导读 （0）
文档反馈