Open In App

MySQL FOREIGN KEY Constraint

Last Updated : 30 Jan, 2024
Improve
Improve
Like Article
Like
Save
Share
Report

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:

user-table

orders output:
order-table

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:

emp-table

EMPLOYEE TABLE

Output of department table:

dept-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:

updated-table

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:

updated-table

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.



Previous Article
Next Article

Similar Reads

How to Temporarily Disable a Foreign Key Constraint in MySQL?
MySQL is a popular open-source relational database management system (RDBMS) that is uniquely used to construct expandable and high-productivity databases. MySQL, which was created by MySQL AB and later acquired by its current owner Oracle Corporation, was originally introduced in 1995. MySQL is reputed for its sturdy and quick functioning attribut
4 min read
MySQL NOT NULL Constraint
In the database management system maintaining data reliability and data accuracy is very important. MySQL is a popular relational database management system, which offers various constraints to provide security and ensure the integrity of the stored data. There are various key constraints present in the table among these, the NOT NULL Constraint en
3 min read
MYSQL CHECK Constraint
MySQL is a very famous and widely used open-source RDBMS. It is used to store, retrieve, and manage structured data efficiently. It is used in both types of applications i.e. large and small scale applications. In MySQL, the CHECK constraint enforces a condition on the column(s) of a table. It makes sure that a specific type of data only gets inser
5 min read
MySQL DEFAULT Constraint
The MySQL DEFAULT constraint returns the default value for a table column. The DEFAULT value of a column is a value used in the case, when there is no value specified by the user. To use this function there should be a DEFAULT value assigned to the column. Otherwise, it will generate an error. SyntaxDEFAULT (column_name) Where, column_name: Name of
3 min read
Create Unique Constraint with NULL Columns in MySQL
Unique constraint in MySQL ensures that each value in the column is unique. If a column contains a NULL value, it is also treated as a unique value, but if a column contains multiple NULL values it can not have a unique constraint. In this article we will look over how we can Create unique constraints with NULL columns in MySQL, using the syntax, m
3 min read
MySQL UNIQUE Constraint
MySQL UNIQUE constraint ensures that the values in a column or group of columns remain unique, preventing duplicate entries in a column and maintaining the integrity of the table. UNIQUE Constraint in MySQLA UNIQUE constraint in MySQL prevents two records from having identical values in a column. A UNIQUE constraint can contain null values as long
4 min read
SQLite Foreign Key
SQLite is a serverless architecture, which does not require any server or administrator to run or process queries. This database system is used to develop embedded software due to its lightweight, and low size. It is used in Desktop applications, mobile applications televisions, and so on. Foreign Key in SQLiteA Foreign Key is a column or set of co
3 min read
Foreign Key in MariaDB
MariaDB is an open-source database system which is similar to MySQL. It provide various features such as high availability and vertical scalability to allow database to scale up over various nodes or single node as features like Galera Cluster in MariaDB. The Foreign keys are the most important features that help define and establish relationships
6 min read
Foreign Key with a Null Value in PostgreSQL
PostgreSQL's support for nullable foreign key columns provides flexibility in cases where child table rows may not reference specific parent table rows. However, managing these nullable foreign keys requires attention to maintain data integrity. Explore the methods and implications of implementing nullable foreign keys in PostgreSQL. When using nul
5 min read
Foreign Key Indexing and Performance in PostgreSQL
As the world of databases is always changing, PostgreSQL is one of the autonomous options because of its dependability, capability to handle huge amounts of data, and fast performance. Effective indexing, especially for foreign keys, is an important key for enhancing the speed of PostgreSQL. Appropriate indexes for foreign keys can greatly speed up
4 min read