MySQL Foreign Key

Last Updated: 24 June, 2023

In MySQL, a foreign key is a column or a set of columns in a table that refers to the primary key or a unique key in another table. It establishes a relationship between two tables, known as the parent table and the child table. The foreign key constraint ensures referential integrity, meaning that values in the foreign key column(s) must match the values in the primary key or unique key column(s) of the referenced table.

To create a foreign key in MySQL, we need to follow these steps:

First, Create the parent table:

 CREATE TABLE parent_table (
  id INT PRIMARY KEY,
  ...
 );

Second, Create the child table with a foreign key column:

 CREATE TABLE child_table (
  id INT PRIMARY KEY,
  parent_id INT,
  ...
  FOREIGN KEY (parent_id) REFERENCES parent_table(id)
 );

In the example above, child_table has a foreign key column parent_id, which references the id column of the parent_table. This establishes a one-to-many relationship, where each row in child_table can only refer to an existing row in parent_table.

By creating the foreign key constraint, MySQL ensures that any value inserted or updated in the parent_id column of child_table must exist in the id column of parent_table. If you try to insert a value that does not exist in the referenced table, or if you try to delete a row from the parent table that has associated rows in the child table, MySQL will raise an error.

Foreign keys can also have additional options, such as ON DELETE and ON UPDATE, to specify the actions to be taken when the referenced row is deleted or updated. The available options are CASCADE, SET NULL, RESTRICT, and NO ACTION, each with its own behavior.

It's worth noting that both the child and parent tables must use the InnoDB storage engine in MySQL for foreign key constraints to be supported. InnoDB is the default storage engine in recent versions of MySQL.

Foreign keys are a powerful tool for maintaining data integrity and enforcing relationships between tables in a relational database like MySQL.

Remove Foreign key constraint

Foreign key constraints can also be modified or removed using the ALTER TABLE statement. For example, to drop a foreign key constraint, we can use the following syntax:

 ALTER TABLE student
 DROP FOREIGN KEY fk_address_id;