MariaDB Xpand Foreign Keys

Applications can use foreign keys to define referential constraints between different MariaDB 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, then Xpand can block the change to protect child rows, or Xpand can propagate the change to the child rows.

Overview

MariaDB Xpand supports foreign key constraints:

  • The behavior of foreign keys with MariaDB Xpand is identical to the behavior of foreign keys with InnoDB.

  • Both the parent table and the child table must use Xpand.

This documentation applies to both Xpand topologies. Most examples are designed for the Xpand Performance Topology. However, the examples explicitly mention any differences that are relevant for the Xpand Storage Engine Topology.

Foreign keys are not currently supported on columns that have columnar indexes.

Examples

Create Tables with a Foreign Key Constraint

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

Note

If you are using an Xpand Storage Engine Topology, you must also specify ENGINE=Xpand, unless default_storage_engine is set to Xpand.

Term Definitions

Term

Definition

parent table

A table that has a foreign key.

child table

A table that is referenced by a foreign key.

Uses Cases for Foreign Keys

Foreign keys allow the database to handle certain types of referential constraint checks, so that the application does not have to handle them. As a consequence, application logic can be simplified.

Some example use cases are described below.

Managing Accounts Use Case

Many different applications have account management features. Foreign keys can be used to simplify certain aspects of managing accounts, such as:

  • If an account is deleted, foreign keys can be used to automatically delete data associated with the account.

  • If an account is deleted, foreign keys can be used to automatically disassociate its data, so that it is no longer associated with any account.

  • If an account's user name or user ID is updated, foreign keys can be used to automatically associate its data with the new user name or user ID.

Managing Store Inventory Use Case

Foreign keys can also be used to simplify certain aspects of managing a store inventory, such as:

  • If a product is deleted, foreign keys can be used to automatically delete data associated with the product, such as reviews and images.

  • If a product's name is changed, foreign keys can be used to automatically associate the new name with the product's other data.

  • If a customer purchases a product, foreign keys can be used to associate the order with the product.

  • If a customer reviews a product, foreign keys can be used to associate the review with the product.

Operating on Xpand Tables with Foreign Keys

When an Xpand table has a foreign key or when it is referenced by a foreign key, Xpand performs a referential constraint check when certain types of operations try to write to the table. The specific details depend on whether the table is a parent table or a child table.

Operating 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. The following table describes the details:

Operation

Result of Constraint Check

Action

Consequence

UPDATE

Xpand finds corresponding rows in the child table

ON UPDATE RESTRICT

UPDATE

Xpand finds corresponding rows in the child table

ON UPDATE NO ACTION

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

DELETE

Xpand finds corresponding rows in the child table

ON DELETE NO ACTION

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

Operating 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. The following table describes the details:

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

Creating Xpand Tables with a Foreign Key Constraint

Let's create Xpand tables with a foreign key constraint:

  1. Connect to the server using MariaDB Client:

    $ mysql --user=root --password --no-auto-rehash
    
  2. Use the CREATE DATABASE statement to create the database:

    CREATE DATABASE hq_sales;
    

    Note

    In a Xpand Storage Engine Topology, the ES nodes do not synchronize databases automatically. MariaDB Replication can be used to synchronize databases and other objects. Without replication, the database needs to be created separately on each ES node.

  3. Use the CREATE TABLE statement to create the parent table:

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

    Note

    If you are using an Xpand Storage Engine Topology, you must also specify ENGINE=Xpand, unless default_storage_engine is set to Xpand.

  4. Use the CREATE TABLE statement to create the child table:

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

    Note

    If you are using an Xpand Storage Engine Topology, you must also specify ENGINE=Xpand, unless default_storage_engine is set to Xpand.

  5. Insert some rows into the parent table using the INSERT statement:

    INSERT INTO hq_sales.customers (customer_id, name)
       VALUES
       (1, 'John Doe'),
       (2, 'Jane Doe');
    
  6. Insert a row into the child table for each row in the parent table using the INSERT statement:

    INSERT INTO hq_sales.invoices
       (branch_id, customer_id, invoice_date, invoice_total, payment_method)
    VALUES
       (1, 1, '2020-05-10 12:35:10', 1087.23, 'CREDIT_CARD'),
       (1, 2, '2020-05-10 14:17:32', 1508.57, 'WIRE_TRANSFER');
    
  7. Attempt to delete a row from the parent table that has a corresponding row in the child table using the DELETE statement:

    DELETE FROM hq_sales.customers
       WHERE customer_id = 1;
    

    This will fail with the ER_ROW_IS_REFERENCED_2 error code as explained in the Operating on a Parent Table section:

    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`))
    
  8. Attempt to insert a row into the child table for a non-existent row in the parent table using the INSERT statement:

    INSERT INTO hq_sales.invoices
       (branch_id, customer_id, invoice_date, invoice_total, payment_method)
    VALUES
       (1, 3, '2020-05-10 14:25:16', 227.15, 'CASH');
    

    This will fail with the ER_NO_REFERENCED_ROW_2 error code as explained in the Operating on a Child Table section:

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

Adding a Foreign Key Constraint to an Xpand Table

Let's create Xpand tables after confirming that the default storage engine is InnoDB, and then let's add a foreign key constraint between them:

  1. Connect to the server using MariaDB Client:

    $ mysql --user=root --password --no-auto-rehash
    
  2. Use the CREATE DATABASE statement to create the database:

    CREATE DATABASE hq_sales;
    

    Note

    In a Xpand Storage Engine Topology, the ES nodes do not synchronize databases automatically. MariaDB Replication can be used to synchronize databases and other objects. Without replication, the database needs to be created separately on each ES node.

  3. Use the CREATE TABLE statement to create the parent table:

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

    Note

    If you are using an Xpand Storage Engine Topology, you must also specify ENGINE=Xpand, unless default_storage_engine is set to Xpand.

  4. Use the CREATE TABLE statement to create the child table:

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

    Note

    If you are using an Xpand Storage Engine Topology, you must also specify ENGINE=Xpand, unless default_storage_engine is set to Xpand.

  5. Use the ALTER TABLE statement alter the child table to add the foreign key constraint:

    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;
    
  6. Insert some rows into the parent table using the INSERT statement:

    INSERT INTO hq_sales.customers (customer_id, name)
       VALUES
       (1, 'John Doe'),
       (2, 'Jane Doe');
    
  7. Insert a row into the child table for each row in the parent table using the INSERT statement:

    INSERT INTO hq_sales.invoices
       (branch_id, customer_id, invoice_date, invoice_total, payment_method)
    VALUES
       (1, 1, '2020-05-10 12:35:10', 1087.23, 'CREDIT_CARD'),
       (1, 2, '2020-05-10 14:17:32', 1508.57, 'WIRE_TRANSFER');
    
  8. Attempt to delete a row from the parent table that has a corresponding row in the child table using the DELETE statement:

    DELETE FROM hq_sales.customers
       WHERE customer_id = 1;
    

    This will fail with the ER_ROW_IS_REFERENCED_2 error code as explained in the Operating on a Parent Table section:

    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`))
    
  9. Attempt to insert a row into the child table for a non-existent row in the parent table using the INSERT statement:

    INSERT INTO hq_sales.invoices
       (branch_id, customer_id, invoice_date, invoice_total, payment_method)
    VALUES
       (1, 3, '2020-05-10 14:25:16', 227.15, 'CASH');
    

    This will fail with the ER_NO_REFERENCED_ROW_2 error code as explained in the Operating on a Child Table section:

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

Dropping a Foreign Key Constraint from an Xpand Table

Let's drop the foreign key constraint from the child table created in the Creating Xpand Tables with a Foreign Key Constraint section:

  1. Connect to the server using MariaDB Client:

    $ mysql --user=root --password --no-auto-rehash
    
  2. Obtain the name of the foreign key constraint by querying 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 |
    +-----------------------+
    
  3. Drop the foreign key constraint from the child table using the ALTER TABLE statement:

    ALTER TABLE hq_sales.invoices DROP FOREIGN KEY fk_invoices_customers;
    

Temporarily Disabling 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. Foreign key constraint checks can be temporarily disabled by setting the session value for the foreign_key_checks system variable. 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.

Let's temporarily disable foreign key constraint checks, and then perform some tasks with the tables created in the Creating Xpand Tables with a Foreign Key Constraint section:

  1. Connect to the server using MariaDB Client:

    $ mysql --user=root --password --no-auto-rehash
    
  2. Temporarily disable foreign key constraint checks by setting the foreign_key_checks system variable with the SET SESSION statement:

    SET SESSION foreign_key_checks=OFF;
    
  3. If you want to test how to introduce inconsistencies, then attempt to delete a row from the parent table that has a corresponding row in the child table using the DELETE statement:

    DELETE FROM hq_sales.customers
       WHERE customer_id = 1;
    

    This operation would usually fail with the ER_NO_REFERENCED_ROW error code as explained in the Operating on a Parent Table section, but if foreign key constraint checks are disable, then it will succeed.

  4. If you want to test how to introduce inconsistencies, then also attempt to insert a row into the child table for a non-existent row in the parent table using the INSERT statement:

    INSERT INTO hq_sales.invoices
       (branch_id, customer_id, invoice_date, invoice_total, payment_method)
    VALUES
       (1, 3, '2020-05-10 14:25:16', 227.15, 'CASH');
    

    This operation would usually fail with the ER_NO_REFERENCED_ROW_2 error code as explained in the Operating on a Child Table section, but if foreign key constraint checks are disable, then it will succeed.

  5. Re-enable foreign key constraint checks by setting the foreign_key_checks system variable with the SET SESSION statement:

    SET SESSION foreign_key_checks=ON;