MySQL FOREIGN KEY Constraint

FOREIGN KEY is a field in a table that refers to a unique key in another table. It is used for linking one or more than one table together. Another name for FOREIGN KEY is referencing key. It creates a link (relation) between two tables thus creating referential integrity.

The FOREIGN KEY creates a relationship between the columns in the current table or let’s say table A (the one with the foreign key) and the referenced table or table B (the one with the unique key). This creates a parent-child type of relationship where the table with the FOREIGN KEY in the child table refers to the primary or unique key column in the parent table.

How to Define FOREIGN KEY in MySQL

A FOREIGN KEY constraint is a relational database constraint that ensures the referential integrity between two tables. It establishes a link between a column or a set of columns in one table, called the child table, and the primary key or unique key columns in another table, known as the parent table.

CREATE method can be used to define your FOREIGN KEY in MYSQL:

CREATE TABLE child_table (

child_id INT PRIMARY KEY,

parent_id INT,

other_columns DATATYPE,

FOREIGN KEY (parent_id) REFERENCES parent_table(parent_id)

);

child_table: The name of the table where the foreign key is being defined.

child_id: The primary key column of the child_table.

parent_id: The foreign key column in the child_table that refers to the primary key in the parent table.

Foreign Key Example

let us understand this with an example

We are two tables namely Users and Orders. The Users table will be the parent table, as it contains the PRIMARY KEY (user_ID) and referenced by foreign key (User_ID). And Orders table will be the child table.

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

users Output:

orders output:

Table Structure Verification

Table structure verfication can be done by using some SQL quiries. The follwing quries can help you inspect the structure of the tables and also confirm if there is a foreign key available:

1. Show table

This lists all the tables in your database

SHOW TABLES;

2. Describe Table

The structure of specific table can be viewed with this query.

DESCRIBE <table_name>;

or

SHOW COLUMNS FROM <table_name>;

3. Show Foreign Keys

Now the foreign keys in a table can be listed using the following query

SELECT
  CONSTRAINT_NAME,
  COLUMN_NAME,
  REFERENCED_TABLE_NAME,
  REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  TABLE_SCHEMA = 'database_name' ;

Foreign Key Example Using SET NULL Action

The SET NULL action in a foreign key constraint is used to delete or update row in the parent table while setting the foreign key column in the child table to NULL. [NULL here refers to empty or not known]

Let’s me simplify this with help of an example:

We have two tables: employees and departments. Employee table has the information about the employees along with the dept_id (the department to which they belong).

The departments table has the department name and it’s corresponding id, in the dept_id column. (for ex. 101 for HR, 102 for IT…)

The employees table has the dept_id as foreign key that refers to the primary key (dept_id) in the departments table. Now in a scenario where the a department is deleted, the corresponding dept_id in the employees table will automatically changed NULL.

So even when a department is deleted, we still have record of the employees that were from that department. This is an important rule that helps us to retain data and avoid errors in the employee table even when something in the departments table is changed or deleted.

-- I Created the departments table

CREATE TABLE departments (
    dept_id INT PRIMARY KEY,  
    department_name VARCHAR(50) NOT NULL
);

-- then created the employees table with a foreign key using SET NULL action

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50) NOT NULL,
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id) ON UPDATE SET NULL ON DELETE SET NULL

);

-- Sample data insertion into the tables 

INSERT INTO departments (dept_id, department_name) VALUES
    (1, 'HR'),
    (2, 'Engineering'),
    (3, 'Marketing');

INSERT INTO employees (emp_id, emp_name, dept_id) VALUES
    (101, 'John Doe', 1),
    (102, 'Jane Smith', 2),
    (103, 'Bob Johnson', 3);


-- Display data in the employees table and  department table
SELECT * FROM employees;
SELECT * FROM departments;

Output of employees table:

EMPLOYEE TABLE

Output of department table:

DEPARTMENT TABLE

Now let’s us update the department_id in the departments table and set it to NULL in employees table

UPDATE departments SET dept_id = 4 WHERE dept_id = 2;
SELECT * FROM employees;

Output:

The deleted department was set to NULL

Now let’s us delete a department and set corresponding dept_id to NULL in employees table

DELETE FROM departments WHERE dept_id = 1;
SELECT * FROM employees;

Output:

How to DROP Foreign Key

The foreign key can be dropped using the DROP FOREIGN KEY clause. Here’s the basic syntax:

ALTER TABLE <table_name>

DROP FOREIGN KEY foreign_key_name;

Define Foreign Key Using ALTER TABLE Statement

We can define a foreign key using the ALTER TABLE statement in MySQL. This is the case where you already have a table and need to get a foreign key in it.

The foreign key constraint helps maintain referential integrity between the two tables.

The following syntax can be used:

ALTER TABLE child_table

ADD FOREIGN KEY (child_column)

REFERENCES parent_table (parent_column);

Let us apply this in an example and understand the working

We will create two tables orders and customers

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(50) NOT NULL
);


-- table without a foreign key
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT
);

Now in the orders table, let us add the foreign key using ALTER TABLE

ALTER TABLE orders
ADD FOREIGN KEY (customer_id)
REFERENCES customers(customer_id);

Foreign Key Checks

Foreign key checks are rulebooks/ mechanisms that ensure the consistency of relationships between tables. These helps us maintain referential integrity, preventing actions that could disturb the relationships.

Let us understand the concept of foreign key checks.

Foreign key checks are in two modes: Enabled or Disabled.

To enable foreign key checks:

SET foreign_key_checks = 1;

To disable foreign key checks:

SET foreign_key_checks = 0;

The foreign key checks help us to make sure that every record has a valid corresponding value in the other table. It’s like double-checking to be sure everything is in order. In the most cases it is suggested to keep the checks enabled.

Now when we need to perform some special actions like bulk updation, insertion or deletion we may need to disable these checks temporaily to avoid errors. It’s crucial to re-enable foreign key checks promptly after the bulk update or deletion to ensure that data integrity is restored.

Conclusion

In conclusion, effectively utilizing foreign keys in MySQL is important for designing robust and well-structured relational databases. Foreign keys serve as a very helpful tool in maintaining referential integrity, ensuring that relationships between tables are consistent and reliable.