OceanBase系统架构外键约束

OceanBase 数据库 MySQL 模式支持外键，允许跨表交叉引用相关数据，外键约束有助于保持相关数据的一致性。

外键约束的特性

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

在 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 | NO ACTION | SET DEFAULT

外键约束的相关特性如下：

• 身份标识

• 条件和限制

• 引用行为

身份标识

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

• 如果 CONSTRAINT symbol已定义，则使用该值。

• 如果 CONSTRAINT symbol 子句未定义，或者 CONSTRAINT 关键字后面未包含符号 ，则会自动生成约束名称名称。

• 该值（如果已定义）在数据库中必须是唯一的。重复的值会导致以下内容的错误：ERROR 1005 (HY000): Can't create table 'test.fk1' (errno: 121)。

FOREIGN KEY ... REFERENCES 子句中的表和列标识符可以用反引号 ( `)引用。

条件和限制

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

• 创建外键约束需要有 REFERENCES 父表的权限。父表上被外键所引用的列上需要有主键或者唯一索引。

• 外键和引用键中对应的列必须具有相似的数据类型。INTEGER 和 DECIMAL 等固定精度类型的大小和符号必须相同。字符串类型的长度不必相同。对于非二进制（字符）字符串列，字符集和排序规则必须相同。

• MySQL 模式下需要外键和引用键的索引，以便外键检查可以快速并且不需要表扫描。在引用表中，必须有一个索引，其中外键列以相同的顺序列为 第一 列。如果引用表不存在，则会在引用表上自动创建此类索引。如果您创建另一个可用于强制执行外键约束的索引，则此索引可能会在稍后被静默删除。如果指定 index_name，则如前所述的方法进行使用。

• 不支持外键列的索引前缀。因此，BLOB 和 TEXT 列不能被包括在一个外键，因为对这些列的索引必须总是包含一个前缀长度。

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

引用行为

当 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 子句，则级联操作将失败并显示错误。

  注意

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

• RESTRICT：拒绝父表的删除或更新操作。

  指定 RESTRICT(或 NO ACTION) 与省略 ON DELETE 或 ON UPDATE 子句相同。

• NO ACTION: 标准 SQL 中的关键字。

  在 OceanBase 数据库 MySQL 模式中，相当于 RESTRICT。如果被引用的表中存在相关的外键值，OceanBase 数据库将拒绝对父表的删除或更新操作。有些数据库系统有延迟检查，并且 NO ACTION 是延迟检查。在 MySQL 中，会立即检查外键约束，因此 NO ACTION 与 RESTRICT 含义相同。

对于未指定的 ON DELETE 或 ON UPDATE，默认操作始终为 NO ACTION。默认情况下，显式指定的 ON DELETE NO ACTION 或 ON UPDATE NO ACTION 子句不会出现在 SHOW CREATE TABLE 的输出中。

外键约束的常用操作

添加外键约束

使用如下 ALTER TABLE 语法向现有表添加外键约束：

ALTER TABLE table_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 table_name DROP FOREIGN KEY fk_symbol;

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

外键检查

外键检查由 foreign_key_checks 变量控制，该变量默认启用。通常，在正常操作期间启用此变量以强制执行参照完整性。

在以下情况下禁用 foreign_key_checks 会对数据库带来正面影响：

• 删除由外键约束引用的表。只有在禁用 foreign_key_checks 后才能删除引用的表。删除表时，表上定义的约束也会被删除。

• 以不同于外键关系所需的顺序重新加载表。

• 进行数据导入操作时，可以关闭外键检查，以加速数据导入。

• 对具有外键关系的表执行 ALTER TABLE 操作。

同样，禁用 foreign_key_checks 也会带来一些负向影响：

• 允许删除包含带有外键的表的数据库，这些表被数据库外的表引用。

• 允许删除带有其他表引用的外键的表。

启用 foreign_key_checks 不会触发表数据的扫描，这意味着当 foreign_key_checks 重新启用时，在禁用 foreign_key_checks 时添加到表中的行不会检查一致性。

外键定义和元数据

要查看外键定义，请使用 SHOW CREATE TABLE，示例如下：

obclient> 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_OBFK_1633952161788605` FOREIGN KEY (`parent_id`) REFERENCES `test`.`parent`(`id`) ON UPDATE RESTRICT ON DELETE CASCADE ,
KEY `par_ind` (`parent_id`) BLOCK_SIZE 16384 GLOBAL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0

可以从 INFORMATION_SCHEMA.KEY_COLUMN_USAGE 表中获取有关外键的信息。查询示例如下：

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

外键约束示例

• 通过单列外键关联父表和子表。

  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 表具有其他两个表的外键。一个外键引用产品表中的两列索引，另一个引用客户表中的单列索引。

  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 DELETE RESTRICT ON UPDATE CASCADE,
   FOREIGN KEY (customer_id) REFERENCES customer (id)
  )