FOREIGN KEY Constraints with MariaDB Xpand

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

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:

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