MySQL Foreign Key

Introduction

In this chapter, we will learn about the foreign key in MySQL. A foreign key is a column or a set of columns in one table that references the primary key columns of another table. Foreign keys establish and enforce a link between the data in the two tables, ensuring referential integrity. We will cover the syntax for defining foreign keys, examples, cascading actions, and important considerations for using foreign keys in MySQL.

Syntax

The basic syntax for defining a foreign key in MySQL is:

When creating a table:

CREATE TABLE table_name ( column1 datatype, column2 datatype, ... FOREIGN KEY (foreign_key_column) REFERENCES parent_table(parent_key_column) [ON DELETE action] [ON UPDATE action] ); 

Adding a foreign key to an existing table:

ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (foreign_key_column) REFERENCES parent_table(parent_key_column) [ON DELETE action] [ON UPDATE action]; 

Example

Let’s go through an example where we create two tables with foreign keys to establish relationships between them, including cascading actions.

Full Example

1. Create a Database:

CREATE DATABASE company; 

2. Select the Database:

USE company; 

3. Create the Departments Table with a Primary Key:

CREATE TABLE departments ( id INT PRIMARY KEY AUTO_INCREMENT, department_name VARCHAR(100) NOT NULL ); 

4. Insert Data into the Departments Table:

INSERT INTO departments (department_name) VALUES ('Mathematics'), ('Science'), ('Literature'); 

5. Create the Employees Table with a Foreign Key:

CREATE TABLE employees ( id INT PRIMARY KEY AUTO_INCREMENT, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, email VARCHAR(100) UNIQUE, department_id INT, FOREIGN KEY (department_id) REFERENCES departments(id) ); 

6. Insert Data into the Employees Table:

INSERT INTO employees (first_name, last_name, email, department_id) VALUES ('Rahul', 'Sharma', 'rahul.sharma@example.com', 1), ('Priya', 'Singh', 'priya.singh@example.com', 2), ('Amit', 'Kumar', 'amit.kumar@example.com', 1), ('Neha', 'Verma', 'neha.verma@example.com', 3), ('Sahil', 'Mehta', 'sahil.mehta@example.com', 2); 

7. Query the Tables:

Let’s perform a simple query to verify our data.

SELECT e.first_name, e.last_name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.id; 

Output

first_name	last_name	department_name
Rahul	Sharma	Mathematics
Priya	Singh	Science
Amit	Kumar	Mathematics
Neha	Verma	Literature
Sahil	Mehta	Science

Cascading Actions with Foreign Key

Cascading actions help maintain referential integrity when rows are deleted or updated in the parent table.

ON DELETE CASCADE

If you delete a row from the parent table, all related rows in the child table will also be deleted.

ON UPDATE CASCADE

If you update a primary key value in the parent table, the corresponding foreign key values in the child table will also be updated.

Example with Cascading Actions

1. Create the Employees Table with Cascading Actions:

CREATE TABLE employees ( id INT PRIMARY KEY AUTO_INCREMENT, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, email VARCHAR(100) UNIQUE, department_id INT, FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE CASCADE ON UPDATE CASCADE ); 

2. Insert Data into the Employees Table:

INSERT INTO employees (first_name, last_name, email, department_id) VALUES ('Rahul', 'Sharma', 'rahul.sharma@example.com', 1), ('Priya', 'Singh', 'priya.singh@example.com', 2), ('Amit', 'Kumar', 'amit.kumar@example.com', 1), ('Neha', 'Verma', 'neha.verma@example.com', 3), ('Sahil', 'Mehta', 'sahil.mehta@example.com', 2); 

3. Delete a Row in the Parent Table:

DELETE FROM departments WHERE id = 1; 

4. Query the Tables Again:

SELECT e.first_name, e.last_name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.id; 

Output

first_name	last_name	department_name
Priya	Singh	Science
Neha	Verma	Literature
Sahil	Mehta	Science

In this example, deleting the department with id = 1 (Mathematics) also deletes the employees Rahul Sharma and Amit Kumar who were in that department, demonstrating the ON DELETE CASCADE action.

Adding a Foreign Key to an Existing Table

Let’s assume we have a table projects and we want to add a foreign key to reference the employees table.

1. Create the Projects Table:

CREATE TABLE projects ( project_id INT PRIMARY KEY AUTO_INCREMENT, project_name VARCHAR(100) NOT NULL, employee_id INT ); 

2. Add a Foreign Key to the Projects Table:

ALTER TABLE projects ADD CONSTRAINT fk_employee FOREIGN KEY (employee_id) REFERENCES employees(id); 

3. Insert Data into the Projects Table:

INSERT INTO projects (project_name, employee_id) VALUES ('Project A', 1), ('Project B', 2), ('Project C', 3), ('Project D', 4); 

4. Query the Tables:

Let’s perform a simple query to verify our data.

SELECT p.project_name, e.first_name, e.last_name FROM projects p JOIN employees e ON p.employee_id = e.id; 

Output

project_name	first_name	last_name
Project A	Rahul	Sharma
Project B	Priya	Singh
Project C	Amit	Kumar
Project D	Neha	Verma

Important Considerations

• Referential Integrity: Foreign keys ensure that the relationship between tables remains consistent. For example, you cannot have an employee_id in the projects table that does not exist in the employees table.
• Cascading Actions: Define cascading actions (ON DELETE CASCADE, ON UPDATE CASCADE) to automatically handle changes in the parent table.
• Indexing: Foreign key columns should be indexed for performance reasons, especially if the table is large and frequently queried.

Conclusion

The foreign key is a critical component for establishing and enforcing relationships between tables in MySQL. This chapter covered the syntax for defining foreign keys, provided a full example with and without cascading actions, and discussed important considerations.