Manage foreign key constraints - PolarDB

Foreign keys enforce referential integrity between tables in PolarDB-X. Because checking and maintaining foreign key constraints on partitioned tables is more complex than in standalone databases, foreign keys can cause performance degradation. Run full verification and testing before using foreign keys in performance-sensitive scenarios.

Enable foreign keys

Use the following statement to enable or disable the foreign key feature:

SET [GLOBAL] enable_foreign_key = [true | false];

Create a foreign key

Syntax

[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

Naming rules

If you specify a name in the CONSTRAINT clause, that name becomes the foreign key name.
If you omit the name, PolarDB-X generates one automatically.
index_name names the index created alongside the foreign key, not the foreign key itself.
Foreign key names must be unique within the table.

Supported database modes

Foreign keys work in both AUTO mode and DRDS mode databases. You can also create foreign keys that reference tables in a database of a different mode (AUTO referencing DRDS, or vice versa).

Limitations

Limitation	Details
Storage engine	InnoDB only
Column data types	Foreign key columns must match the parent table columns in data type, size, precision, length, charset, and collation
Column count	The number of columns in the foreign key must equal the number of referenced columns in the parent table
Name length	Foreign key names, index names, table names, and column names: 64 characters maximum
Index order	Index columns must appear in the same order as the foreign key columns. If the child table has no index on the foreign key columns, PolarDB-X creates one automatically
Prefix indexes	Not supported. Foreign keys on `BLOB` and `TEXT` columns are not supported
`SET NULL`	The referenced column cannot be `NOT NULL` (for example, a primary key)
`SET DEFAULT`	Not supported
Generated columns	Foreign keys on generated columns (stored, virtual, and logical) are not supported — this differs from standard MySQL behavior

Examples (AUTO mode)

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

Multiple foreign keys

The product_order table references two parent tables. The first foreign key references a two-column index in product; the second references a single-column index in 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)
);

Add a foreign key to an existing table

Use ALTER TABLE to add a foreign key constraint to an existing 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]

Before running this statement, create an index on the column that the foreign key references. A foreign key can also reference an index on the same table.

Drop a foreign key

Use ALTER TABLE to drop a foreign key:

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

Use the foreign key name as fk_symbol. If you did not specify a name when creating the constraint, use the auto-generated name. To find the name, run 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

Referential actions

Referential actions control what happens to child table rows when a referenced value in the parent table is updated or deleted.

Action	Behavior
`NO ACTION`	_Default action._ Rejects the delete or update on the parent table if a matching foreign key value exists in the child table. Alias: `RESTRICT`
`RESTRICT`	Same behavior as `NO ACTION`
`CASCADE`	Deletes or updates matching rows in the child table when the parent row is deleted or updated
`SET NULL`	Sets the foreign key columns in the child table to `NULL` when the parent row is deleted or updated. Fails if the child column is `NOT NULL`
`SET DEFAULT`	This action will be supported soon

Foreign key checks

PolarDB-X enforces foreign key checks by default. Use the foreign_key_checks parameter to disable checks at the global or session level.

When to disable foreign key checks

Dropping a parent table that is referenced by a foreign key
Importing data when tables are created in a different order than their foreign key dependencies require — disabling checks also speeds up import
Loading data into a child table before the parent table data is available
Running ALTER TABLE statements that involve foreign key columns

What remains enforced when checks are disabled

If a table restructuring makes a foreign key definition invalid, an error is still reported
Dropping an index required by a foreign key still requires you to drop the foreign key first
Creating a foreign key that violates its own constraints still reports an error
Dropping the database that contains a parent table referenced by a cross-database foreign key still reports an error

Additional implications of disabling checks

A table that has foreign keys referencing other tables, and is also referenced by foreign keys in other tables, can be dropped.
Re-enabling foreign_key_checks does not scan existing data. Integrity is not verified for rows inserted while checks were disabled.

Locking

When an INSERT or UPDATE on a child table triggers a foreign key check, PolarDB-X locks the referenced row in the parent table for the duration of the transaction. This is equivalent to a SELECT FOR UPDATE on that row, and prevents concurrent operations from modifying the parent row in a way that would violate the constraint.

In high-concurrency write scenarios where most child table rows reference the same parent row, this can cause significant lock contention.

View foreign key definitions and metadata

SHOW CREATE TABLE

Run SHOW CREATE TABLE to see the foreign key definition:

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

Run SHOW FULL CREATE TABLE to see whether the foreign key is logical or physical, along with the full partition structure:

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` */

The /* TYPE LOGICAL */ annotation identifies a logical foreign key.

INFORMATION_SCHEMA tables

Query the following system tables for foreign key metadata:

INFORMATION_SCHEMA.TABLE_CONSTRAINTS
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
INFORMATION_SCHEMA.INNODB_SYS_FOREIGN
INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS

Examples:

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

View the execution plan for cascading operations

Run EXPLAIN to see which foreign keys are involved in a cascading operation. The >> Foreign Key field identifies each constraint in the cascade chain:

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

Run EXPLAIN COST to estimate the cost of the cascading operation before executing it:

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

For more information about EXPLAIN, see EXPLAIN.

Compatibility

Physical and logical foreign keys

When you create a foreign key, PolarDB-X determines whether to push it down to the storage layer based on the table structure. Foreign keys pushed down to the storage layer are called physical foreign keys; those that remain at the PolarDB-X layer are called logical foreign keys. Physical foreign keys generally perform better.

Logical foreign keys are created by default in two situations: you drop and rebuild the parent table while foreign key checks are disabled, or you create the child table with a foreign key before the parent table exists.

MySQL compatibility

Matching methods

PolarDB-X supports the MATCH SIMPLE matching method, which is the MySQL default. The matching method cannot be changed. MATCH PARTIAL will be supported later.

Inner-join REFERENCES

MySQL and PolarDB-X parse but ignore REFERENCES clauses defined inline on a column (not as part of a FOREIGN KEY constraint). The following example shows this behavior:

CREATE TABLE parent (
    id INT KEY
);

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

SHOW CREATE TABLE child;

The output shows that child contains no foreign key:

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