FOREIGN KEY Constraints with MariaDB Enterprise Server

Compatibility

• MariaDB Enterprise Server 10.2

• MariaDB Enterprise Server 10.3

• MariaDB Enterprise Server 10.4

• MariaDB Enterprise Server 10.5

• MariaDB Enterprise Server 10.6

Term Definitions

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.

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.

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

<table_name>_ibfk_<constraint_count>

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.