FOREIGN KEY in MySQL

A FOREIGN KEY in MySQL is a constraint that establishes a link between two tables. It simply connects a column in one table to the PRIMARY KEY or UNIQUE KEY in another table.

A FOREIGN KEY is a column or group of columns in one table that refers to the PRIMARY KEY in another table. The table with the foreign key column is called the child table, and the table with the primary key column is called the referenced or parent table.

Thus, it creates a relationship between a parent table and a child table. The foreign key constraint ensures that the value inserted into the child table already exists in the parent table.

Why Use FOREIGN KEY Constraint?

MySQL uses FOREIGN KEY constraints to:

• Maintain data consistency between related tables.
• Prevent invalid data insertion into the foreign key column (in the child tables).
• Create relationships between multiple tables.
• Improve database integrity and reliability.
• Prevent orphan records in the database.
• Enforce referential integrity automatically.
• Prevents deleting a record in the parent table if related rows still exist in the child table.

Examples of FOREIGN KEY Relationships

• A student must belong to an existing department.
• An order must belong to an existing customer.
• An employee must belong to an existing company.
• A product category must exist before assigning products to it.
• A book must belong to an existing author.

These examples clearly show how FOREIGN KEY constraints maintain relationships between parent and child tables in relational databases.

Syntax of FOREIGN KEY Constraint in MySQL

The general syntax of foreign key constraint in MySQL is:

CREATE TABLE child_table (
    column_name1 data_type1,
    column_name2 data_type2,

    FOREIGN KEY (foreign_key_column)
    REFERENCES parent_table(parent_column)
);

In this syntax:

• FOREIGN KEY (foreign_key_column): This statement defines the column with a foreign key in the child table.
• REFERENCES parent_table(parent_column): This statement specifies the parent table name and the parent column referenced by the foreign key. The referenced column is usually a PRIMARY KEY or UNIQUE KEY.

Rules of FOREIGN KEY in MySQL

There are the following rules of defining FOREIGN KEY constraint in MySQL:

• The parent column must be PRIMARY KEY or UNIQUE.
• Both columns must have compatible data types.
• The referenced table must exist.

Examples of FOREIGHN KEY Constraint

Let us see an example in which we will create a parent table named departments and a child table named employees.

Parent Table

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

Child Table

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    department_id INT,

    FOREIGN KEY (department_id)
    REFERENCES departments(department_id)
);

In this example:

• departments is the name of the parent table.
• department_id is the column with the PRIMARY KEY constraint in the departments table.
• employees is the name of the child table.
• department_id in the employees table is the column with the FOREIGN KEY constraint.
• The foreign key connects the employees table with the departments table.
• This relationship ensures that every employee belongs to a valid department.

Insert Data into Parent Table

INSERT INTO departments VALUES
(1, 'HR'),
(2, 'IT'),
(3, 'Finance');

Insert Data into Child Table

INSERT INTO employees VALUES
(101, 'John', 1),
(102, 'David', 2);

Output:

Data inserts successfully because department IDs 1 and 2 exist in the departments table.

Invalid FOREIGN KEY Example

INSERT INTO employees VALUES
(103, 'Smith', 10);

Output:

Error Code: 1452
Cannot add or update a child row:
a foreign key constraint fails

In this example, department ID 10 does not exist in the parent table. Therefore, MySQL does not allow the insertion of invalid data into the child table.

FOREIGN KEY with CONSTRAINT Name in MySQL

In MySQL, you can assign a custom name to a FOREIGN KEY constraint using the CONSTRAINT keyword. A custom constraint name helps identify, manage, and remove the foreign key easily later. You should always use the meaningful constraint names, which improves database readability and maintenance.

The general syntax of FOREIGN KEY with constraint name in MySQL is:

CREATE TABLE child_table (
    column_name1 data_type1,
    column_name2 data_type2,

    CONSTRAINT constraint_name
    FOREIGN KEY (foreign_key_column)
    REFERENCES parent_table(parent_column)
);

In the above syntax, the CONSTRAINT keyword specifies a custom name for the foreign key constraint.

Example: FOREIGN KEY with CONSTRAINT Name

Parent Table

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

Child Table

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    department_id INT,

    CONSTRAINT fk_department
    FOREIGN KEY (department_id)
    REFERENCES departments(department_id)
);

In this example, fk_department is the custom name of the foreign key constraint.

Adding FOREIGN KEY to Existing Table

You can also add a foreign key constraint to an existing table using ALTER TABLE. Let’s take an example based on it.

Example:

ALTER TABLE employees
ADD CONSTRAINT fk_department
FOREIGN KEY (department_id)
REFERENCES departments(department_id);

Dropping FOREIGN KEY in MySQL

Sometimes, you may need to remove an existing FOREIGN KEY constraint from a table. In MySQL, you can remove a foreign key using the ALTER TABLE statement with the DROP FOREIGN KEY clause. Dropping a foreign key removes the link between the parent table and the child table.

ALTER TABLE table_name
DROP FOREIGN KEY constraint_name;

Difference Between PRIMARY KEY and FOREIGN KEY in MySQL

There are the following differences between PRIMARY KEY and FOREIGN KEY in MySQL: