外键约束

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 父表的权限。父表上被外键所引用的列上需要有主键或者索引。

  说明

  OceanBase 数据库支持非唯一索引上创建外键约束。

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

  说明

  OceanBase 数据库外键和引用键的类型可以是：

  • UNSIGNED FLOAT 和 FLOAT。
  • FLOAT 和 UNSIGNED FLOAT
  • UNSIGNED DOUBLE 和 DOUBLE。
  • DOUBLE 和 UNSIGNED DOUBLE。

• OceanBase 数据库 MySQL 模式会默认对引用键（父键）创建索引，不会默认对外键创建索引。

• 不支持外键列的索引前缀。因此，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 子句，则级联操作将失败并显示错误。

• 对于外键自引用的情况，且某一行引用自己，并且是 delete cascade 行为时，OceanBase 数据库 MySQL 模式可以成功删除这一行。

• 对于外键自引用，如果某一行的父键被引用，OceanBase 数据库 MySQL 模式允许级联更新。

  注意

  OceanBase 数据库 MySQL 模式外键级联操作会激活触发器。

• 如果外键存在环状引用，或实际更新时出现了重复更新一个表的情况，系统将触发错误（错误码 1451）。具体说明和示例如下：

  1. 自引用外键的级联更新限制：当表中存在自引用外键时，如果更新操作导致子列尝试级联更新父列本身，则会触发错误。示例如下：

     //创建表
     DROP TABLE IF EXISTS t1;
     CREATE TABLE t1(c1 INT UNIQUE, c2 INT, FOREIGN KEY (c2) REFERENCES t1(c1) ON UPDATE CASCADE);
     //插入测试数据
     INSERT INTO t1 VALUES(1, NULL), (2, 1);
     

     执行以下更新操作：

     UPDATE t1 SET t1.c1 = 10 WHERE t1.c1 = 1;
     

     返回结果如下：

     ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
     

     原因分析：

     • 表 t1 中的外键 c2 引用了自身的 c1 列。
     • 更新 c1 的值会触发对 c2 的级联更新，但由于 c2 是自引用外键，系统检测到潜在的循环更新，因此报错。

  2. 多表环状引用的级联更新限制：当多个表之间存在环状引用时，如果级联更新形成了闭环（即更新路径返回到“正在更新”的表），系统将触发错误。示例如下：

     //创建表
     DROP TABLE IF EXISTS t1, t2, t3;
     CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT);
     CREATE TABLE t2(c1 INT PRIMARY KEY, c2 INT UNIQUE);
     CREATE TABLE t3(c1 INT PRIMARY KEY, c2 INT UNIQUE);
     //创建外键约束
     ALTER TABLE t1 ADD FOREIGN KEY(c2) REFERENCES t3(c2) ON UPDATE CASCADE;
     ALTER TABLE t2 ADD FOREIGN KEY(c2) REFERENCES t1(c1) ON UPDATE CASCADE;
     ALTER TABLE t3 ADD FOREIGN KEY(c2) REFERENCES t2(c2) ON UPDATE CASCADE;
     //插入测试数据
     INSERT INTO t1 VALUES(1, NULL);
     INSERT INTO t2 VALUES(1, 1);
     INSERT INTO t3 VALUES(1, 1);
     INSERT INTO t1 VALUES(2, 1);
     

     执行以下更新操作：

     UPDATE t1 SET t1.c1 = 10 WHERE t1.c1 = 1;
     

     返回结果如下：

     ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
     

     原因分析：

     • 表 t1、t2 和 t3 形成了环状引用：t1 → t2 → t3 → t1。
     • 更新 t1 的值会依次触发对 t2、t3 和 t1 的级联更新，最终回到 t1，形成闭环，因此报错。

  3. 涉及多表更新的级联更新限制：在涉及多个表的更新操作中，如果 UPDATE 语句直接或间接地触发了对 UPDATE 语句中包含的表的级联更新，则会触发错误。示例如下：

     //创建表
     DROP TABLE IF EXISTS t1, t2, t3;
     CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT);
     CREATE TABLE t2(c1 INT PRIMARY KEY, c2 INT UNIQUE, c3 INT);
     CREATE TABLE t3(c1 INT PRIMARY KEY, c2 INT UNIQUE, c3 INT);
     //创建外键约束
     ALTER TABLE t2 ADD FOREIGN KEY(c2) REFERENCES t1(c1) ON UPDATE CASCADE;
     ALTER TABLE t3 ADD FOREIGN KEY(c2) REFERENCES t2(c2) ON UPDATE CASCADE;
     //插入测试数据
     INSERT INTO t1 VALUES(1, 5);
     INSERT INTO t2 VALUES(1, 1, 5);
     INSERT INTO t3 VALUES(1, 1, 5);
     

     执行以下更新操作：

     UPDATE t1, t2, t3 SET t1.c1 = 10, t2.c1 = 20 WHERE t1.c2 = t2.c3 AND t2.c3 = t3.c3;
     

     返回结果如下：

     ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
     

     原因分析：

     更新 t1 触发外键级联更新 t2、t3，但是 t2 包含在 UPDATE 中，在更新 t2 时，检测到存在隐式环 t1->t2->t3->t2，因此报错。

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