Foreign key constraints

The MySQL-compatible mode of OceanBase Database supports foreign keys, allowing cross-table references to related data. Foreign key constraints help maintain the consistency of related data.

Features

A foreign key relationship involves a parent table that contains the initial column values and a child table that contains column values referencing the parent column values. The foreign key constraint is defined on the child table.

The basic syntax for defining a foreign key constraint in a CREATE TABLE or ALTER TABLE statement is as follows:

​[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

The characteristics of foreign key constraints are as follows:

• Identifiers

• Conditions and limitations

• Referential actions

Identifiers

The naming of foreign key constraints is managed by the following rules:

• If CONSTRAINT symbol is defined, the specified value is used.

• If the CONSTRAINT symbol clause is not defined, or if no symbol follows the CONSTRAINT keyword, a constraint name is automatically generated.

• The value (if defined) must be unique within the database. Duplicate values will result in an error such as ERROR 1005 (HY000): Can't create table 'test.fk1' (errno: 121).

Table and column identifiers in the FOREIGN KEY ... REFERENCES clause can be quoted using backticks (`).

Conditions and limitations

Foreign key constraints are subject to the following conditions and limitations:

• To create a foreign key constraint, you must have the REFERENCES privilege on the parent table. The columns in the parent table referenced by the foreign key must have a primary key or an index.

  Note

  For OceanBase Database V4.3.5, foreign key constraints can be created on non-unique indexes starting from V4.3.5 BP1.

• The corresponding columns in the foreign key and the referenced key must have similar data types. For fixed-precision types such as INTEGER and DECIMAL, the size and sign must match. The lengths of string types do not need to be the same. For non-binary (character) string columns, the character set and collation must be the same.

  Note

  For OceanBase Database V4.3.5, starting from V4.3.5 BP1, the types of foreign keys and referenced keys can include:

  • UNSIGNED FLOAT and FLOAT
  • FLOAT and UNSIGNED FLOAT
  • UNSIGNED DOUBLE and DOUBLE
  • DOUBLE and UNSIGNED DOUBLE

  <

• In OceanBase Database's MySQL-compatible mode, an index is automatically created for the referenced key (parent key) by default, but no index is automatically created for the foreign key.

• Index prefixes are not supported for foreign key columns. Therefore, BLOB and TEXT columns cannot be included in a foreign key because indexes on these columns must always include a prefix length.

• Foreign key constraints cannot reference virtual generated columns.

Referential actions

When an UPDATE or DELETE operation affects key values in the parent table that have matching rows in the child table, the outcome depends on the referential actions specified in the ON UPDATE and ON DELETE clauses of the FOREIGN KEY statement. The referential actions include:

• CASCADE: When a row is deleted or updated in the parent table, the matching rows in the child table are automatically deleted or updated.

  OceanBase Database supports both ON DELETE CASCADE and ON UPDATE CASCADE. Do not define multiple ON UPDATE CASCADE clauses that act on the same column in the parent or child table between two tables. If the two tables in a foreign key relationship are both defined as parent and child for one another, you must define an ON UPDATE CASCADE or ON DELETE CASCADE clause for the other FOREIGN KEY statement to ensure the cascading operation succeeds. If only one FOREIGN KEY statement defines an ON UPDATE CASCADE or ON DELETE CASCADE clause, the cascading operation will fail with an error.

• For self-referencing foreign keys, where a row references itself and the behavior is DELETE CASCADE, OceanBase Database MySQL mode can successfully delete that row.

• For self-referencing foreign keys, if a parent key of a row is referenced, OceanBase Database MySQL mode allows cascading updates.

  Notice

  In OceanBase Database's MySQL-compatible mode, cascading operations for foreign keys will activate any associated triggers.

• If there are circular references in foreign keys, or if duplicate updates to a table occur during actual updates, the system will trigger an error (error code 1451). Specific explanations and examples are as follows:

  1. Limitations on cascading updates for self-referencing foreign keys: When a table contains a self-referencing foreign key, if an update operation causes the child column to attempt a cascading update on the parent column itself, an error will be triggered. Here is an example:

     // Create a table.
     DROP TABLE IF EXISTS t1;
     CREATE TABLE t1(c1 INT UNIQUE, c2 INT, FOREIGN KEY (c2) REFERENCES t1(c1) ON UPDATE CASCADE);
     // Insert test data.
     INSERT INTO t1 VALUES(1, NULL), (2, 1);
     

     Perform the following update operation:

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

     The return result is as follows:

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

     Cause analysis:

     • The foreign key c2 in table t1 references its own c1 column.
     • Updating the value of c1 triggers a cascading update on c2. However, since c2 is a self-referencing foreign key, the system detects a potential circular update and reports an error.

  2. Limitations on cascading updates for circular references across multiple tables: When circular references exist between multiple tables, and if a cascading update forms a closed loop (i.e., the update path returns to the table being updated), the system will trigger an error. Here is an example:

     // Create tables.
     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);
     // Create foreign key constraints.
     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 test data.
     INSERT INTO t1 VALUES(1, NULL);
     INSERT INTO t2 VALUES(1, 1);
     INSERT INTO t3 VALUES(1, 1);
     INSERT INTO t1 VALUES(2, 1);
     

     Perform the following update operation:

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

     The return result is as follows:

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

     Cause analysis:

     • Tables t1, t2, and t3 have cyclic foreign key references: t1 → t2 → t3 → t1.
     • Updating the t1 table will trigger cascading updates in the order of the t2, t3, and t1 tables. Since the updates return to the t1 table, a loop is formed, and therefore an error is returned.

  3. Limitations on cascading updates involving multiple tables: In update operations involving multiple tables, if an UPDATE statement directly or indirectly triggers a cascading update on a table that is included in the UPDATE statement, an error will be triggered. Here is an example:

     // Create tables.
     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);
     // Create foreign key constraints.
     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 test data.
     INSERT INTO t1 VALUES(1, 5);
     INSERT INTO t2 VALUES(1, 1, 5);
     INSERT INTO t3 VALUES(1, 1, 5);
     

     Perform the following update operation:

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

     The return result is as follows:

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

     Cause analysis:

     Updating the t1 table triggers cascading updates for the t2 and t3 tables. However, the t2 table is included in the UPDATE statement. An implicit cycle t1->t2->t3->t2 is detected during the update of the t2 table, and therefore an error is returned.

• RESTRICT: Prohibits delete or update operations on the parent table.

  Specifying RESTRICT (or NO ACTION) is equivalent to omitting the ON DELETE or ON UPDATE clause.

• NO ACTION: A keyword in standard SQL.

  In OceanBase Database's MySQL-compatible mode, NO ACTION is equivalent to RESTRICT. If there are related foreign key values in the referenced table, OceanBase Database will reject delete or update operations on the parent table. Some database systems have deferred checks, and NO ACTION is a deferred check. In MySQL, foreign key constraints are checked immediately, so NO ACTION and RESTRICT have the same meaning.

For unspecified ON DELETE or ON UPDATE, the default action is always NO ACTION. By default, the explicitly specified ON DELETE NO ACTION or ON UPDATE NO ACTION clauses do not appear in the output of SHOW CREATE TABLE.

Common foreign key operations

Add a foreign key constraint

You can execute the following ALTER TABLE statement to add a foreign key constraint to an existing 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]

A foreign key can reference the same table. When using ALTER TABLE to add a foreign key constraint to a table, make sure to first create an index on the column referenced by the foreign key.

Drop a foreign key constraint

You can execute the following ALTER TABLE statement to drop a foreign key constraint:

​ALTER TABLE table_name DROP FOREIGN KEY fk_symbol;

If a CONSTRAINT name is defined in the FOREIGN KEY clause when you create a constraint, you can reference that name to drop the foreign key constraint. Otherwise, a constraint name will be generated internally, and you must use that value. To determine the foreign key constraint name, use SHOW CREATE TABLE.

Enable foreign key checks

Foreign key checks are controlled by the foreign_key_checks variable, which is enabled by default. Typically, this variable is enabled during normal operations to enforce referential integrity.

Disabling foreign_key_checks can have positive effects on the database in the following situations:

• Dropping tables referenced by foreign key constraints. Referenced tables can only be dropped after foreign_key_checks is disabled. When a table is dropped, the constraints defined on it are also removed.

• Reloading tables in an order different from that required by foreign key relationships.

• During data import operations, foreign key checks can be turned off to speed up the data import process.

• Performing ALTER TABLE operations on tables with foreign key relationships.

Similarly, disabling foreign_key_checks can also have some negative effects:

• It allows the deletion of databases containing tables with foreign keys that are referenced by tables outside the database.

• It allows the deletion of tables with foreign keys that are referenced by other tables.

Enabling foreign_key_checks does not trigger a scan of table data, meaning that rows added to tables while foreign_key_checks are disabled will not be checked for consistency when foreign_key_checks is re-enabled.

Query foreign key definitions and metadata

You can use the SHOW CREATE TABLE statement to query foreign key definitions. Here is an example:​

​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

You can query the INFORMATION_SCHEMA.KEY_COLUMN_USAGE table for metadata about foreign keys. Here is an example:

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

Foreign key examples

• Use a single-column foreign key to associate a parent table with a child table.

  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
  )
  

• The product_order table has foreign keys to two other tables. One foreign key references a two-column index in the product table, and the other references a single-column index in the customer table.

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