`FOREIGN KEY` Constraints with MariaDB Xpand

This page is part of MariaDB's Documentation.

The parent of this page is: Constraints for MariaDB Xpand

Topics on this page:

Overview
Compatibility
CREATE TABLE
ALTER TABLE
- ALTER TABLE .. ADD CONSTRAINT .. FOREIGN KEY
- ALTER TABLE .. DROP FOREIGN KEY
Query Constraint Name
Constraint Checks
- Constraint Checks on a Parent Table
- Constraint Checks on a Child Table
Temporarily Disable FOREIGN KEY Constraint Checks
Special Cases

Overview

MariaDB Xpand supports FOREIGN KEY constraints to define referential constraints between Xpand tables:

Referential constraints allow the database to automatically ensure that each row in the child table is associated with a valid row in the parent table
If the row in the parent table changes, Xpand can block the change to protect child rows, or Xpand can propagate the change to the child rows
For easy migrations from InnoDB, Xpand handles foreign keys similar to InnoDB

Compatibility

MariaDB Xpand 5.3
MariaDB Xpand 6.0
MariaDB Xpand 6.1

`CREATE TABLE`

To create tables with a foreign key constraint, use the CREATE TABLE statement with the CONSTRAINT ... FOREIGN KEY (...) REFERENCES ... clause:

CREATE TABLE hq_sales.customers (
   customer_id BIGINT AUTO_INCREMENT NOT NULL,
   customer_name VARCHAR(500) NOT NULL,
   customer_email VARCHAR(200),
   PRIMARY KEY(customer_id)
);

CREATE TABLE hq_sales.invoices (
   invoice_id BIGINT AUTO_INCREMENT NOT NULL,
   branch_id INT NOT NULL,
   customer_id BIGINT,
   invoice_date DATETIME(6),
   invoice_total DECIMAL(13, 2),
   payment_method ENUM('NONE', 'CASH', 'WIRE_TRANSFER', 'CREDIT_CARD', 'GIFT_CARD'),
   PRIMARY KEY(invoice_id),
   CONSTRAINT fk_invoices_customers
      FOREIGN KEY (customer_id)
      REFERENCES hq_sales.customers (customer_id)
      ON DELETE RESTRICT
      ON UPDATE RESTRICT
);

`ALTER TABLE`

`ALTER TABLE .. ADD CONSTRAINT .. FOREIGN KEY`

With MariaDB Xpand, the ALTER TABLE statement can be used to add a foreign key using the ADD FOREIGN KEY clause with an optional constraint name:

ALTER TABLE hq_sales.invoices
   ADD CONSTRAINT fk_invoices_customers
      FOREIGN KEY (customer_id)
      REFERENCES hq_sales.customers (customer_id)
      ON DELETE RESTRICT
      ON UPDATE RESTRICT;

`ALTER TABLE .. DROP FOREIGN KEY`

With MariaDB Xpand, the ALTER TABLE statement can be used to drop a foreign key using the DROP FOREIGN KEY clause:

ALTER TABLE hq_sales.invoices
   DROP FOREIGN KEY fk_invoices_customers;

Query Constraint Name

MariaDB Xpand allows the name of a foreign key constraint to be queried using the information_schema.TABLE_CONSTRAINTS table:

SELECT CONSTRAINT_NAME
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'hq_sales'
AND TABLE_NAME = 'invoices'
AND CONSTRAINT_TYPE = 'FOREIGN KEY';

+-----------------------+
| CONSTRAINT_NAME       |
+-----------------------+
| fk_invoices_customers |
+-----------------------+

Constraint Checks

When a table is a parent table or a child table in a foreign key relationship, Xpand performs a referential constraint check for write operations.

Constraint Checks on a Parent Table

When an Xpand table has a foreign key, it is known as a parent table. Xpand performs referential constraint checks for the following operations on parent tables:

UPDATE
DELETE
TRUNCATE (if the table has foreign keys, Xpand executes it as a DELETE statement with no WHERE clause)

When Xpand performs a referential constraint check, the outcome depends on several factors:

Operation	Result of Constraint Check	Action	Consequence
UPDATE	Xpand finds corresponding rows in the child table	`ON UPDATE RESTRICT`	`ER_ROW_IS_REFERENCED_2` error code
UPDATE	Xpand finds corresponding rows in the child table	`ON UPDATE NO ACTION`	Fails with `ER_ROW_IS_REFERENCED_2` error code
UPDATE	Xpand finds corresponding rows in the child table	`ON UPDATE CASCADE`	Success Row in the parent table is updated Corresponding rows in the child table are also updated with the new foreign key value
UPDATE	Xpand finds corresponding rows in the child table	`ON UPDATE SET NULL`	Success Row in the parent table is updated Corresponding rows in the child table are also updated with `NULL`
UPDATE	Xpand does not find any corresponding rows in the child table	N/A	Success Row in the parent table is updated
DELETE	Xpand finds corresponding rows in the child table	`ON DELETE RESTRICT`	Fails with `ER_ROW_IS_REFERENCED_2` error code
DELETE	Xpand finds corresponding rows in the child table	`ON DELETE NO ACTION`	Fails with `ER_ROW_IS_REFERENCED_2` error code
DELETE	Xpand finds corresponding rows in the child table	`ON DELETE CASCADE`	Success Row in the parent table is deleted Corresponding rows in the child table are also deleted
DELETE	Xpand finds rows in the child table for the row	`ON DELETE SET NULL`	Success Row in the parent table is deleted Corresponding rows in the child table are updated with `NULL`
DELETE	Xpand does not find any rows in the child table for the row	N/A	Success Row in the parent table is deleted

For example, when an operation fails with the ER_ROW_IS_REFERENCED_2 error code, the following error message can occur:

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
   (`hq_sales`.`invoices`, CONSTRAINT `fk_invoices_customers`
   FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`))

Constraint Checks on a Child Table

When an Xpand table is referenced by a foreign key, it is known as a child table. Xpand performs referential constraint checks for the following operations on child tables:

When Xpand performs a referential constraint check, the outcome depends on several factors:

Operation	Result of Constraint Check	Consequence
INSERT	New foreign key value is present in parent table	Success
INSERT	New foreign key value is not present in parent table	Fails with `ER_NO_REFERENCED_ROW_2` error code
INSERT	New foreign key value is `NULL`	Success
UPDATE	New foreign key value is present in parent table	Success
UPDATE	New foreign key value is not present in parent table	Fails with `ER_NO_REFERENCED_ROW_2` error code
UPDATE	New foreign key value is `NULL`	Success
DROP TABLE	Table is referenced by a foreign key	Fails with `ER_NO_REFERENCED_ROW_2` error code

For example, when an operation fails with the ER_NO_REFERENCED_ROW_2 error code, the following error message can occur:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
   (`hq_sales`.`invoices`, CONSTRAINT `fk_invoices_customers`
   FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`))

Temporarily Disable `FOREIGN KEY` Constraint Checks

When performing bulk data loads, dropping a table, or rebuilding a table, it can improve performance to temporarily disable FOREIGN KEY constraint checks:

If FOREIGN KEY constraint checks are temporarily disabled, then rows can be inserted that violate the constraint, so users must proceed with caution
This feature is most useful when you are loading a data set that is known to be valid
FOREIGN KEY constraint checks can be temporarily disabled by setting the session value for the foreign_key_checks system variable to OFF:
```
SET SESSION foreign_key_checks=OFF;
```

Special Cases

Xpand 6 does not support foreign keys on columns that have Columnar indexes
Xpand does not support the referential action SET DEFAULT
Xpand does not allow foreign keys with non-unique parents with referential actions CASCADE or SET NULL:
- When a foreign key referencing a non-unique parent is created or altered and uses CASCADE or SET NULL, Xpand raises an error message
Xpand does not validate that foreign keys reference columns with the same data type
If dangling foreign key references are created when foreign_key_checks is disabled, subsequent inserts will succeed even if foreign_key_checks is enabled
Xpand allows self-referencing foreign keys
Xpand does not require a key length to be specified when a foreign key references a BLOB or TEXT column
When adding or dropping a foreign key, the reported number of rows affected does not reflect the actual number of rows

FOREIGN KEY Constraints with MariaDB Xpand