MariaDB Enterprise Server InnoDB Foreign Keys

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

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 InnoDB Tables with Foreign Keys

When an InnoDB table has a foreign key or when it is referenced by a foreign key, InnoDB 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 InnoDB table has a foreign key, it is known as a parent table. InnoDB performs referential constraint checks for the following operations on parent tables:

When InnoDB 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

InnoDB finds corresponding rows in the child table

ON UPDATE RESTRICT

UPDATE

InnoDB finds corresponding rows in the child table

ON UPDATE NO ACTION

UPDATE

InnoDB 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. If the child table has an update trigger, the trigger will not be executed for the update.

UPDATE

InnoDB 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. If the child table has an update trigger, the trigger will not be executed for the update.

UPDATE

InnoDB does not find any corresponding rows in the child table

N/A

  • Success

  • Row in the parent table is updated

DELETE

InnoDB finds corresponding rows in the child table

ON DELETE RESTRICT

DELETE

InnoDB finds corresponding rows in the child table

ON DELETE NO ACTION

DELETE

InnoDB 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. If the child table has a delete trigger, the trigger will not be executed for the delete.

DELETE

InnoDB 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. If the child table has an update trigger, the trigger will not be executed for the update.

DELETE

InnoDB 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 InnoDB table is referenced by a foreign key, it is known as a child table. InnoDB performs referential constraint checks for the following operations on child tables:

When InnoDB 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 InnoDB Tables with a Foreign Key Constraint

Let's create InnoDB tables with a foreign key constraint after confirming that the default storage engine is InnoDB:

  1. Connect to the server using MariaDB Client:

    $ mariadb --user=root
    
  2. Confirm that the default storage engine is InnoDB by checking the default_storage_engine system variable using the SHOW SESSION VARIABLES statement:

    SHOW SESSION VARIABLES
       LIKE 'default_storage_engine';
    
    +------------------------+--------+
    | Variable_name          | Value  |
    +------------------------+--------+
    | default_storage_engine | InnoDB |
    +------------------------+--------+
    
  3. If the database does not exist, then create the database for the sequence and table using the CREATE DATABASE statement:

    CREATE DATABASE hq_sales;
    
  4. Create the parent table using the CREATE TABLE statement:

    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)
    );
    
  5. Create the child table using the CREATE TABLE statement:

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

Adding a Foreign Key Constraint to an InnoDB Table

Let's create InnoDB 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:

    $ mariadb --user=root
    
  2. Confirm that the default storage engine is InnoDB by checking the default_storage_engine system variable using the SHOW SESSION VARIABLES statement:

    SHOW SESSION VARIABLES
       LIKE 'default_storage_engine';
    
    +------------------------+--------+
    | Variable_name          | Value  |
    +------------------------+--------+
    | default_storage_engine | InnoDB |
    +------------------------+--------+
    
  3. If the database does not exist, then create the database for the sequence and table using the CREATE DATABASE statement:

    CREATE DATABASE hq_sales;
    
  4. Create the parent table using the CREATE TABLE statement:

    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)
    );
    
  5. Create the child table using the CREATE TABLE statement:

    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)
    );
    
  6. Alter the child table to add the foreign key constraint using the ALTER TABLE statement:

    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;
    
  7. 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');
    
  8. 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');
    
  9. 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`))
    
  10. 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 InnoDB Table

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

  1. Connect to the server using MariaDB Client:

    $ mariadb --user=root
    
  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 InnoDB Tables with a Foreign Key Constraint section:

  1. Connect to the server using MariaDB Client:

    $ mariadb --user=root
    
  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;
    

Special Cases

Foreign Key Constraint Namespace

When a foreign key constraint is created without a name, InnoDB implicitly gives it a name in the format:

<table_name>_ibfk_<constraint_count>

If foreign key constraints are explicitly created with names in the same format, it is possible for collisions to occur during table renames. In that case, the MariaDB Error Log would contain messages like the following:

2021-06-16 11:50:39 139702710404864 [ERROR] InnoDB: Possible reasons:
2021-06-16 11:50:39 139702710404864 [ERROR] InnoDB: (1) Table rename would cause two FOREIGN KEY constraints to have the same internal name in case-insensitive comparison.
2021-06-16 11:50:39 139702710404864 [ERROR] InnoDB: (2) Table `test`.`t3` exists in the InnoDB internal data dictionary though MySQL is trying to rename table `test`.`t2b` to it. Have you deleted the .frm file and not used DROP TABLE?
2021-06-16 11:50:39 139702710404864 [ERROR] InnoDB: If table `test`.`t3` is a temporary table #sql..., then it can be that there are still queries running on the table, and it will be dropped automatically when the queries end. You can drop the orphaned table inside InnoDB by creating an InnoDB table with the same name in another database and copying the .frm file to the current database. Then MySQL thinks the table exists, and DROP TABLE will succeed.

Dependence on an Index

A foreign key constraint requires an index on the column. If a foreign key constraint is added to a column without an index, InnoDB will automatically create an index to enforce the foreign key constraint.

When the foreign_key_checks system variable is disabled, it is possible to drop the index used by a foreign key constraint. When the foreign_key_checks system variable is re-enabled, InnoDB will have no way to enforce the foreign key constraint, so all operations that could potentially violate the foreign key constraint will fail.