Introduction to MySQL Foreign Key Constraint

MySQL has support for foreign keys and foreign key constraints, which help maintain consistency in related data and allow related data to be cross-referenced between tables. A child table containing column values that refer to the parent table's column values and a parent table holding the initial column values are involved in a foreign key relationship. The child table has a defined foreign key constraint.

In simple terms, a table's columns or groups of columns that link to columns or groups in another table are called foreign keys. MySQL preserves referential integrity due to the foreign key constraining the data in the associated tables. The foreign key columns of the child table typically reference the primary key columns of the parent table. A table may include many foreign keys, each of which refers to a primary key found in a separate parent table.

After a foreign key constraint is set up, the values in the foreign key columns from the child table must either be NULL or have an equivalent row in the parent key columns of the parent table.

Self-referencing foreign key

There are situations when the parent and child tablesrefer to the same table. In this instance, the primary key in the same table is referenced by the foreign key.

See the employees table in the example database that follows:

The employeeNumber column, which is the employees table's primary key, is referenced by the reportTo column, a foreign key.
The reporting hierarchy between employees and managers can be stored in the employees table due to this connection. Every employee can report to one or more employees, and an employee can have one or more subordinates.
Recursive or self-referencing foreign keys are those that are found in the column reportTo.

MYSQL Foreign Key Syntax

The CREATE TABLE or ALTER TABLE statement can define a foreign key constraint using the following basic syntax:

[CONSTRAINT constraint_name]


FOREIGN KEY [foreign_key_name] (column_name, ...)


REFERENCES parent_table(colunm_name,...)


[ON DELETE reference_option]


[ON UPDATE reference_option]

In this syntax:

After the CONSTRAINT keyword, first, enter the name of the foreign key constraint you wish to construct. MySQL creates a name for the foreign key constraint automatically if you omit the constraint name.
After the foreign key keywords, provide a list of foreign key columns separated by commas. If you omit the foreign key name, it will be produced automatically and is likewise optional.
The foreign key columns refer to a list of comma-separated columns; thus, the third step is to define the parent table.
Lastly, use the ON DELETE and ON UPDATE clauses to indicate how the foreign key preserves referential integrity between the child and parent tables. The reference_option controls what happens in MySQL when values are updated (ON UPDATE) or removed (ON DELETE) in the parent key columns.

The five reference options available in MySQL are SET DEFAULT, RESTRICT, NO ACTION, CASCADE, and SET NULL.

CASCADE: The term "cascade" refers to the process by which the values of corresponding rows in a child database are automatically updated or removed when a row in the parent table is changed.
SET NULL: A foreign key field (or columns) in the child table has its values set to NULL in the event that a row from the parent table is modified or removed.
RESTRICT: MySQL prevents deleting or editing rows from the parent table if a record from the parent table matches a row in the child table.
NO ACTION: RESTRICT and NO ACTION are synonymous terms.
SET DEFAULT: The MySQL parser is able to identify SET DEFAULT. Both InnoDB and NDB tables, however, refuse this action.

In fact, three operations—RESTRICT, CASCADE, and SET NULL—are completely supported by MySQL.

The default action is RESTRICT if the ON DELETE and ON UPDATE clauses are not specified.

Identifiers of MYSQL Foreign Key Constraints

The following rules apply to the naming of foreign key constraints:

If defined, the value of the CONSTRAINT symbol is utilized.
A constraint name is automatically produced if either the CONSTRAINT symbol clause is not defined or if a symbol is not provided after the CONSTRAINT keyword.
Before MySQL 8.0.16, the InnoDB and NDB storage engines would use the FOREIGN_KEY index_name if defined if the CONSTRAINT symbol clause was not defined or if a symbol was not included after the CONSTRAINT keyword. The FOREIGN_KEY index_name is not used in MySQL versions 8.0.16 and beyond.
Foreign names are stored by the NDB Cluster using the same letter case that was used during creation. Before version 8.0.20, when executing SQL statements such as SELECT, NDB processed the names of foreign keys in those statements and compared them with the names stored in a case-sensitive manner when lower_case_table_names equalled 0.
In a FOREIGN KEY... REFERENCES clause, table and column identifiers may be quoted enclosed in backticks (~). If the ANSI_QUOTES SQL mode is activated, a possible choice is to use double quotation marks ("). It also considers the configuration for the system variable lower_case_table_names.

The following rules and limitations apply to foreign key constraints:

Parent and child tables can't be designated as temporary tables; instead, they must share the same storage engine.
The REFERENCES privilege on the parent table is necessary in order to create a foreign key constraint.
The data types of the corresponding columns in the referred key and the foreign key must match. For fixed precision types like INTEGER and DECIMAL, the size and sign have to match.
MySQL supports foreign key references within a table between different columns (it is not permitted for a column to reference itself via a foreign key). A dependent record inside the same table is referred to in these situations as a "child table record".
For foreign key checks to be quick and avoid the need for a table scan, MySQL has to have indexes on both referred and foreign keys.
The foreign key columns must be listed as the initial columns in the reference table's index in the same sequence. If such an index is absent, it is automatically generated on the referring table.
If another index is created that can be utilized to impose the foreign key requirement, this one might be silently discarded later. If provided, index_name is utilized in the manner previously mentioned.
Any index column, or collection of columns, can be referenced by a foreign key using InnoDB. However, the cited columns have to be the initial columns in the same order in the linked table's index. A consideration also goes to hidden columns that InnoDB appends to an index.
For every column to be referred to as a foreign key in NDB, there must be an explicit unique key, also known as the chief key. The extension of ordinary SQL that is called InnoDB does not.
Foreign key columns do not support index prefixes. As a result, indexes on BLOB and TEXT columns must constantly include a prefix length, so such columns are unable to appear in a foreign key.
Foreign keys are not supported by InnoDB at this time for tables that have user-defined partitioning. Both parent and child tables are included in this.
The only user partitioning types allowed by the NDB storage engine, KEY and LINEAR KEY, do not fall under this constraint. These tables may contain foreign key references or be the targets of such references.
It is not possible to change a table in a foreign key connection to use a different storage engine. You have to remove any foreign key constraints before you can modify the storage engine.
A foreign key constraint cannot reference a virtual-generated column.

MYSQL Foreign Key Examples

First, let us make a new database for the demonstration called foreignkeydemo.

CREATE DATABASE foreignkeydemo;


USE foreignkeydemo;

RESTRICT and NO ACTION actions

Make two tables called categories and products inside the foreignkeydemo database:

CREATE TABLE categories.


  categoryId INT AUTO_INCREMENT PRIMARY KEY,


  categoryName VARCHAR(100) NOT NULL


ENGINE = INNODB;


CREATE TABLE products.


  productId INT AUTO_INCREMENT PRIMARY KEY,


  productName VARCHAR(100) NOT NULL,


  categoryId INT,


  CONSTRAINT fk_category FOREIGN KEY (categoryId)


  REFERENCES categories(categoryId)


ENGINE = INNODB;

The foreign key column in the products table, which refers to the categoryId column in the categories table, is called categoryId.

For both update and delete operations, the default action is RESTRICT, as no ON UPDATE and ON DELETE clauses are specified.

The subsequent steps demonstrate the RESTRICT action.

Add two rows to the categories table as follows:

INSERT INTO categories (categoryName)


VALUES


    ('Smartphone'),


    ('Smartwatch');

Choose information from the table of categories:

SELECT * FROM categories;

Add a new row to the table of products:

INSERT INTO products (productName, categoryId)


VALUES('IQ',1);

The categories table contains category ID 1, and that's why it functions.

Try to add a new row with a categoryId value to the products table if it doesn't already exist in the categories table:

INSERT INTO products (productName, categoryId)


VALUES('Vivo',4);

MySQL reported the subsequent error:

Change the number in the categories table's categoryId column to 100:

UPDATE categories


SET categoryId = 100


WHERE categoryId = 2;

MySQL raised this error:

You are unable to modify or remove categoryId 1 due to the RESTRICT option, as it is referred to by productId 1 in the products table.

CASCADE action

The ON UPDATE CASCADE and ON DELETE CASCADE actions can be seen in action with these stages.

Drop the products table:

DROP TABLE products;

Include the ON UPDATE CASCADE and ON DELETE CASCADE settings for the foreign key in the creation of the products table.

CREATE TABLE products.


    productId INT AUTO_INCREMENT PRIMARY KEY,


    productName varchar(100) not null,


    categoryID INT NOT NULL,


    CONSTRAINT fk_category


    FOREIGN KEY (categoryId)


    REFERENCES categories(categoryId)


        ON UPDATE CASCADE


        ON DELETE CASCADE


) ENGINE=INNODB;

To the products table, add four rows:

INSERT INTO products (productName, categoryId)


VALUES


    ('IQ', 1),


    ('iPhone,1),


    ('Apple Watch',2),


    ('FireBolt',2);

Retrieve information from the product table:

SELECT * FROM products;

Change the categories table's categoryIds from 1 to 100.

UPDATE categories


SET categoryId = 100


WHERE categoryId = 1;

Check out the latest update:

SELECT * FROM categories;

Utilize the products table to extract data:

SELECT * FROM products;

In the products table, the ON change CASCADE action caused two rows with value 1 in the categoryId field to automatically change to 100.

Remove the second category ID from the categories table:

DELETE FROM categories


WHERE categoryId = 2;

Check that it was deleted:

SELECT * FROM categories;

Look over the table of products:

SELECT * FROM products;

Because of the ON DELETE CASCADE action, every product in the products table with category ID 2 was immediately removed.

SET NULL action

These procedures show you how to use the ON UPDATE SET NULL and ON DELETE SET NULL actions.

Remove the product tables and categories.

DROP TABLE IF EXISTS categories;


DROP TABLE IF EXISTS products;

Create the tables for the categories and products:

CREATE TABLE categories.


    categoryId INT AUTO_INCREMENT PRIMARY KEY,


    categoryName VARCHAR(100) NOT NULL


)ENGINE=INNODB;


CREATE TABLE products.


    productId INT AUTO_INCREMENT PRIMARY KEY,


    productName varchar(100) not null,


    categoryId INT,


    CONSTRAINT fk_category


    FOREIGN KEY (categoryId)


        REFERENCES categories(categoryId)


        ON UPDATE SET NULL


        ON DELETE SET NULL


)ENGINE=INNODB;

The ON UPDATE SET NULL and ON DELETE SET NULL options replaced the previous foreign key in the products table.

Add rows to the table of categories:

INSERT INTO categories (categoryName)


VALUES


    ('Smartphone'),


    ('Smartwatch');

Incorporate rows into the table of products:

INSERT INTO products (productName, categoryId)


VALUES


    ('IQ', 1),


    ('iPhone,1),


    ('Apple Watch',2),


    ('FireBolt',2);

Modify categoryId in the categories table from 1 to 100:

UPDATE categories


SET categoryId = 100


WHERE categoryId = 1;

Confirm the update.

SELECT * FROM categories;

Choose information from the product table:

The products table's rows with category ID 1 were automatically set to NULL as a result of the ON UPDATE SET NULL action.

Delete category ID 2 from the table of categories:

DELETE FROM categories


WHERE categoryId = 2;

Check the product table.

SELECT * FROM products;

In the products table, the ON DELETE SET NULL action caused the values in the categoryId column of the rows that had categoryId 2 to be set to NULL automatically.

Adding Foreign Key Constraints

The ALTER TABLE syntax given below can be employed to add a foreign key constraint to an already existing table:

ALTER TABLE tbl_name


    ADD [CONSTRAINT [symbol]] FOREIGN KEY


    [index_name] (col_name, ...)


    REFERENCES tbl_name (col_name,...)


    [ON DELETE reference_option]


    [ON UPDATE reference_option]

A foreign key that refers to the same table may be self-referential. Make sure you first construct an index on the column or columns that the foreign key references when you use ALTER TABLE to add a foreign key constraint to a table.

Dropping Foreign Key Constraints

The ALTER TABLE syntax that follows can be used to remove a foreign key constraint:

ALTER TABLE tbl_name; DROP FOREIGN KEY fk_symbol;

You can remove the foreign key constraint by referring to the CONSTRAINT name that was defined by the FOREIGN KEY clause at the time the constraint was formed. If not, an internal constraint name was generated, and you have to utilize that value. Use SHOW CREATE TABLE to find the name of the foreign key constraint:

mysql> SHOW CREATE TABLE child\G


       Table: child


Create Table: CREATE TABLE `child` (


  `id` int DEFAULT NULL,


  `parent_id` int DEFAULT NULL,


  KEY `par_ind` (`parent_id`),


  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`)


  REFERENCES `parent` (`id`) ON DELETE CASCADE


ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci


mysql> ALTER TABLE child DROP FOREIGN KEY `child_ibfk_1`;

It is possible to add and remove a foreign key within the same ALTER TABLE statement. ALGORITHM = INPLACE. It is not compatible with ALTER TABLE. ALGORITHM=COPY.

Foreign Key Checks

Foreign key constraints can be verified in MySQL tables that are InnoDB or NDB. The variable foreign key check, which is enabled by default, manages foreign key checking. In order to ensure referential integrity, you should normally leave this variable enabled throughout regular operation. NDB tables and InnoDB tables are both impacted by the foreign_key_checks value in the same way.

Supporting both global and session scopes, the foreign_key_checks variable is dynamic.

It is helpful to turn off foreign key checking when:

Deleting a table from which a foreign key constraint is referenced Only after turning off foreign_key_checks may a referenced table be removed. Restrictions that have been defined on a table are likewise removed when the table is dropped.
Reloading tables in a sequence that differs from what their foreign key connections demand.
For instance, the dump file generated by mysqldump contains accurate table definitions, including foreign key constraints for child tables. Mysqldump automatically adds a statement in the dump output that turns off foreign_key_checks, making it easy to reload dump files for tables with foreign key associations.
If any tables in the dump file are incorrectly arranged for foreign keys, you can import the tables in any order, thanks to this. By eliminating foreign key checks, turning off foreign key checks expedites the import process as well.
Using LOAD DATA procedures in order to prevent the use of foreign keys
Executing an ALTER TABLE command on a table with a foreign key association

Foreign key restrictions are disregarded when foreign_key_checks is deactivated, with the following exceptions:

If the foreign key constraints that reference the table are not followed by the table definition when a previously dropped table is recreated, an error is returned. The names and kinds of the columns in the table must be correct.
Indexes on the mentioned keys are also required. In the event that these conditions are not met, MySQL returns Error 1005, referring to Error 150 in the error message, indicating an incorrectly created foreign key constraint.
When a table is changed, if a foreign key definition is not constructed appropriately for the new table, an error (errno: 150) is returned.
Removing an index because a foreign key constraint requires it. To drop the index, first, the foreign key constraint needs to be lifted.
Establishing a foreign key constraint in cases where a column refers to a kind of column that does not match
Additional implications of disabling foreign_key_checks include:
Databases that have foreign keys in them that are referenced by tables outside the database may be dropped.
A table that has foreign keys that are referenced by other tables may be dropped.
While foreign_key_checks is disabled, rows added to a table are not verified for consistency when foreign_key_checks is re-enabled. This is because enabling foreign_key_checks fails to trigger a scan of the table data.

Disabling Foreign Key Checks

Turning off foreign key checks can be quite helpful at times, such as when importing data into a table from a CSV file.

In the event that foreign key checks are not disabled, you will need to load data into parent tables first and then child tables, which can be a laborious process. On the other hand, you can load data into tables in any sequence if you turn off the foreign key checks.

You use the following statement to turn off foreign key checks:

SET foreign_key_checks = 0;

Additionally, you can use the following statement to enable it:

SET foreign_key_checks = 1;

Locking

Tables connected by a foreign key constraint have their metadata locks extended by MySQL as needed. By extending metadata locks, connected tables are shielded from concurrent DDL and DML operations that could cause conflicts. Updates to foreign key metadata upon modification of a parent table are also made possible by this functionality.

Foreign key constraints cause all tables connected to them to be opened and implicitly locked when a table is locked explicitly using lock tables. Related tables are placed under a shared read-only lock (LOCK TABLES READ) in order to perform foreign key checks. On linked tables that are part of the operation, a shared-nothing write lock (LOCK TABLES WRITE) is taken in order to prevent cascade modifications.

MySQL Tutorial

MySQL Join

MySQL Function

Questions

Misc