FOREIGN KEY constraints

The MySQL mode of OceanBase Database supports foreign keys to cross-reference related data between tables. FOREIGN KEYconstraints help keep the related data consistent.

Features

A foreign key relationship involves a parent table and a child table. The parent table has the initial column values and the child table references the column values in the parent table. The FOREIGN KEY constraint is defined on the child table.

The following is the basic syntax that defines a FOREIGN KEY constraint in the CREATE TABLE or ALTER TABLE statement:

​[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 following are the features of FOREIGN KEY constraints:

• Identifiers

• Conditions and restrictions

• Referential actions

Identifiers

The naming of FOREIGN KEY constraints is governed by the following rules:

• If you have defined the CONSTRAINT symbol clause, use the name specified by the clause.

• If you have not defined the CONSTRAINT symbol clause, or you have not entered a symbol after the CONSTRAINT keyword, a name of the FOREIGN KEY constraint is automatically generated.

• The name specified by the clause must be unique in a database. Otherwise, the error ERROR 1005 (HY000): Can't create table 'test.fk1' (errno: 121) is reported.

The table name and column identifier in the FOREIGN KEY ... REFERENCES clause can be enclosed in backticks (`).

Conditions and restrictions

FOREIGN KEY constraints comply with the following conditions and restrictions:

• The creation of FOREIGN KEY constraints requires the REFERENCES privilege on the parent table. In the parent table, a column that is referenced by a foreign key must have a primary key or a unique index.

• Corresponding foreign key columns and referenced key columns must have similar data types. The size and sign of fixed precision types such as INTEGER and DECIMAL must be the same. The length of string types can be different. The character set and collation of non-binary (character) string columns must be the same.

• By default, the MySQL mode of OceanBase Database creates indexes for reference keys (parent keys) and does not create indexes for foreign keys.

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

• FOREIGN KEY constraints cannot reference virtual or generated columns.

Referential actions

When an UPDATE or DELETE operation affects a key value in the parent table that has matching rows in the child table, the result depends on the referential action specified by the ON UPDATE and ON DELETE subclauses of the FOREIGN KEY clause. The following are referential actions:

• CASCADE: Delete or update a row from the parent table and automatically delete or update the matching row in the child table.

  The ON DELETE CASCADE and ON UPDATE CASCADE clauses are supported. Do not define multiple ON UPDATE CASCADE clauses that act on the same column in the parent or child table. If a FOREIGN KEY clause is defined on both tables in a foreign key relationship, making them the parent and child tables, the ON UPDATE CASCADE or ON DELETE CASCADE subclause defined for one FOREIGN KEY clause must be defined for the other. This ensures that cascading operations are successful. If the ON UPDATE CASCADE or ON DELETE CASCADE subclause is only defined for one FOREIGN KEY clause, cascading operations fail and the corresponding error is reported.

• If delete cascade is enabled for a self-referential foreign key and a row references itself, you can drop the row in OceanBase Database's MySQL mode.

• For a self-referential foreign key, if the parent key of a row is referenced, you can update the row in cascading mode in OceanBase Database's MySQL mode.

  Notice

  Cascaded foreign key actions in OceanBase Database's MySQL mode activate triggers.

• RESTRICT: Reject the delete or update operation for the parent table.

  When you specify RESTRICT or NO ACTION, it is equivalent to omitting the ON DELETE or ON UPDATE clause.

• NO ACTION: A keyword from standard SQL.

  For the MySQL mode of OceanBase Database, this action is equivalent to RESTRICT. If the referenced table contains a related foreign key value, OceanBase Database rejects the delete or update operation for the parent table. Some database systems have deferred checks, and NO ACTION is a deferred check. In MySQL mode, FOREIGN KEY constraints are immediately checked, so NO ACTION is equivalent to RESTRICT.

For unspecified ON DELETE or ON UPDATE clauses, NO ACTION is always performed by default. By default, an explicitly specified ON DELETE NO ACTION or ON UPDATE NO ACTION clause does not appear in the output of SHOW CREATE TABLE.

Common operations

Add a FOREIGN KEY constraint

You can use the following ALTER TABLE syntax to add a FOREIGN KEY constraint to a 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]

The foreign key can be self-referential, which means referring to the same table. Before you use the ALTER TABLE syntax to add a foreign key constraint to a table, make sure that you have created an index on the columns referenced by the foreign key.

Drop a FOREIGN KEY constraint

You can use the following ALTER TABLE syntax to drop a FOREIGN KEY constraint from a table:

​ALTER TABLE table_name DROP FOREIGN KEY fk_symbol;

If you define a CONSTRAINT name 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 is internally generated, and you must use this name. To obtain the FOREIGN KEY constraint name, use SHOW CREATE TABLE.

Foreign key check

Foreign key checking is controlled by the foreign_key_checks variable. By default, the variable is enabled. This variable is often enabled during normal operations to enforce referential integrity.

In the following scenarios, disabling foreign_key_checks is useful:

• Drop a table referenced by a FOREIGN KEY constraint. The table can only be dropped after you disable foreign_key_checks. Constraints defined on a table are dropped together with the table.

• Reload tables in an order different from the order required by their foreign key relationships.

• Import data. You can disable foreign key checking to speed up data import.

• Execute ALTER TABLE operations on a table in a foreign key relationship. ​

Disabling foreign_key_checks has the following negative effects:

• You are allowed to drop a database that contains tables with foreign keys that are referenced by tables outside the database.

• You are allowed to drop a table with foreign keys referenced by other tables. ​

A table scan is not triggered when foreign_key_checks is enabled. Therefore, rows added to a table during the period when foreign_key_checks is disabled are not checked for consistency when foreign_key_checks is re-enabled.

Foreign key definitions and metadata

To view foreign key definitions, use SHOW CREATE TABLE. 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 information about foreign keys from the INFORMATION_SCHEMA.KEY_COLUMN_USAGE table. 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 |
+--------------+------------+-------------+-----------------------------+

Examples

• The following example shows how to relate parent and child tables by using a single-column foreign key:

  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
  )
  

• In the following example, which is more complex, the product_order table has foreign keys for two other tables. One foreign key references a two-column index in the product table, whereas 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)
  )