This topic describes how to use foreign keys.
Important
Because the implementation of checking and maintaining foreign key constraints on partitioned tables is more complex than that of standalone databases, unreasonable foreign keys may result in high performance overheads and significant system throughput degradation. Before you use data in performance-sensitive scenarios, we recommend that you perform full verification and comprehensive testing on data.
Create a foreign key
Enable the foreign key feature
You can use the following parameter to enable and disable the foreign key feature.
SET [GLOBAL] enable_foreign_key = [true | false]
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
Foreign keys must conform to the following naming rules:
If a name is specified in the CONSTRAINT statement, that name is used.
If no name is specified in the CONSTRAINT statement, a name is automatically generated and used.
The index_name parameter is used only as the name of an index created along with a foreign key and is not the foreign key name.
Foreign key names must be unique in the current table.
Supported database types
Foreign keys can be created for databases in both AUTO mode and DRDS mode. Foreign keys can also be created between AUTO and DRDS databases.
Limits
You can create foreign keys only on databases that use InnoDB.
The columns in the foreign key must be of the same data type as those in the parent table. Their size, precision, length, charset, and collation must be identical.
The columns in the foreign key must correspond to the columns in the parent table. The same number of columns must be created in the foreign key and parent table.
Foreign key names, index names, table names, and column names can be up to 64 characters in length.
The columns in the foreign key must use the same index as those in the parent table. The columns in the index must be in the same order as those in the foreign key. This ensures that the index can be used in checking foreign key constraints, instead of scanning the full table.
If the child table does not have a foreign key index, an index is automatically created. The index name can be customized or be the same as the foreign key name.
Prefix indexes are not supported. Therefore, you cannot create foreign keys on columns of the BLOB and TEXT data types.
The reference column of a foreign key whose reference method is SET NULL cannot be NOT NULL (primary key).
You cannot create a foreign key whose reference method is SET DEFAULT.
Limits different from MySQL
You cannot create foreign keys on generated columns (stored, virtual, and logical).
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 ON UPDATE and ON DELETE subclauses of the FOREIGN KEY clause. Referential actions include:
Parameter
| Description
|
ON DELETE NO ACTION/ ON UPDATE NO ACTION
| The delete or update operation for the parent table is immediately rejected if a related foreign key value exists in the referenced table. This is the default action.
|
ON DELETE RESTRICT/ ON UPDATE RESTRICT
| The alias of ON DELETE NO ACTION or ON UPDATE NO ACTION. |
ON DELETE CASCADE/ ON UPDATE CASCADE
| Deletes or updates the row from the parent table and automatically deletes or updates the matching rows in the child table if a related foreign key value exists in the referenced table.
|
ON DELETE SET NULL/ ON UPDATE SET NULL
| Deletes or updates the row from the parent table and sets the foreign key columns in the child table to NULL if a related foreign key value exists in the referenced table. If the columns in the child table are NOT NULL, the update operation fails.
|
ON DELETE SET DEFAULT/ ON UPDATE SET DEFAULT
| This action will be supported soon. |
Foreign key constraint examples (for AUTO mode)
All examples in this topic demonstrates the foreign key feature in AUTO mode.
The following example creates a foreign key on a single column in the parent and child tables:
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 following example creates two foreign keys in the product_order table. The first foreign key references the two-column index in the product table. The second foreign key references the 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 UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY (customer_id)
REFERENCES customer(id)
);
Create foreign key constraints
You can create foreign key constraints to an existing table by using the following ALTER TABLE statement:
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]
A foreign key can reference the index of the same table. When you execute the ALTER TABLE statement to create a foreign key constraint in a table, you must first create an index on the column that is referenced by the foreign key.
Delete foreign key constraints
You can delete foreign key constraints from an existing table by using the following ALTER TABLE statement:
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;
If you specify a foreign key name when you create a foreign key constraint, you can delete the foreign key constraint by using the specified name. Otherwise, you can delete the foreign key constraint only by using the automatically generated name. You can view foreign key names by executing the SHOW CREATE TABLE statement.
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
Foreign key checks
PolarDB-X supports foreign key checks. You can use the foreign_key_checks parameter to disable the foreign key check feature, which is enabled by default. Enabling the foreign key check feature generally can ensure foreign key reference integrity.
The foreign_key_checks parameter can be effective at the global or session level.
Disabling the foreign key check feature is useful in the following cases:
You must disable the foreign key check feature before you can delete a parent table referenced by a foreign key.
When you import data to the database and if tables are created in different order from that foreign keys depend, an error occurs. You must disable the foreign key check feature before tables can be created. In addition, disabling the foreign key check feature also speed up data import when you import data.
When you import data to the database, an error is reported for the data that has been imported into the child table. You must disable the foreign key check feature before data is imported into the child table.
You must disable the foreign key check feature before you can execute the ALTER TABLE statement concerning foreign keys.
When the foreign key check feature is disabled, foreign key checks are ignored, with the following exceptions:
If restructuring a table causes foreign key definitions to be incorrect, an error is still reported.
If you want to delete an index that is required for a foreign key, you must delete the foreign key first. When you delete an index that is required for a foreign key, an error is reported.
If the conditions or limits of a foreign key are not met when you create the foreign key, an error is still reported.
When you delete the database where the parent table referenced by a cross-database foreign key resides, an error is reported.
Disabling the foreign key check feature has these additional implications:
When a table contains a foreign key that references other tables and that is referenced by foreign keys in other tables, it can still be deleted.
Enabling the foreign key check feature does not scan the data in the table. Therefore, when you enable the foreign key check feature again, integrity is not check for the data added to the table when the foreign key check feature is disabled.
Locking
When the INSERT or UPDATE statement is executed on a child table, the foreign key constraint checks whether the corresponding foreign key value exists in the parent table, starts a transaction, and locks the row in the parent table. This prevents the foreign key value from being modified by other operations which may destroy the foreign key constraint. This locking is equivalent to executing the SELECT FOR UPDATE statement on the row where the foreign key value in the parent table is located. In scenarios where data is concurrently written to a child table, if most of the referenced foreign key values are the same, severe lock conflict may occur.
Foreign key definitions and metadata
To view the foreign key definition, execute the SHOW CREATE TABLE or SHOW FULL CREATE TABLE (confirming a logical foreign key or a physical foreign key) statement.
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
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 ,
GLOBAL INDEX `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
You can also obtain information about foreign keys can from the following system tables:
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
INFORMATION_SCHEMA.INNODB_SYS_FOREIGN
INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS
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 foreign key cascading
You can execute the EXPLAIN statement to view the execution plan that may be involved in foreign key cascading. The Foreign Key field indicates the foreign key to be checked.
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 |
+
You can also execute the EXPLAIN COST statement to estimate costs and avoid high performance overheads caused by complex cascading operations.
For more information, see EXPLAIN.
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 |
+
Compatibility
Foreign key checks and pushdown
When you create a foreign key, PolarDB-X determines whether to push down the foreign key based on the table structure. Foreign keys that are pushed down are called physical foreign keys, and foreign keys that are not pushed down are called logical foreign keys. Pushing down foreign keys improves performance.
Logical foreign keys are created by default in the following cases: you delete and rebuild the parent table after the foreign key check feature is disabled, or you create a child table containing foreign keys first while the parent table structure remains unknown.
Compatibility with MySQL
Matching methods
In MySQL, foreign key constraints support three matching methods: MATCH SIMPLE, MATCH FULL, and MATCH PARTIAL. MATCH SIMPLE is used by default. MATCH PARTIAL will be supported later. PolarDB-X supports the default MATCH SIMPLE method. You cannot modify the matching method.
Inner-join REFERENCES
MySQL and PolarDB-X both can parse but ignore foreign keys defined by inner-joining REFERENCES. The REFERENCES parameter is checked and executed only when it is part of the foreign key definition. The following example uses only REFERENCES when a foreign key constraint is defined.
CREATE TABLE parent (
id INT KEY
);
CREATE TABLE child (
id INT,
pid INT REFERENCES parent(id)
);
SHOW CREATE TABLE child;
The result indicates that the child table does not contain any foreign keys.
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
Elastic Compute Service (ECS)
Container Compute Service (ACS)
