How To Handle Foreign Key Constraints In MySQL

Introduction

This article will explore the basics of foreign key constraints in MySQL, including what they are, why they are essential, and how to create and manage them. We'll also cover some common issues that occur when working with foreign keys and how to handle them.

What is MySQL?

MySQL is an acronym for My Structured Query Language and a database management system. In this MySQL, SQL is a programming language used to work with data in relational databases. SQL is the most common standardized language used to access databases. To better understand MySQL, you can read this article- Introduction To MySQL. And to learn how to work in MySQL Workbench- How to Use MySQL Workbench.

What is Foreign Key in MySQL?

A foreign key in MySQL is a column or a group of columns in a table that refers to the primary key of another table. It establishes a relationship between two tables in a database, which helps maintain data integrity and consistency.

Example.

• If you have two tables in your database i.e. Employee and Department, and you want to link them, you can create a foreign key in the Department table that refers to the primary key in the Employee table. This will allow you to track which Employee is placed in a particular Department and ensure that the data remains accurate and consistent.
• In MySQL, you can create a foreign key constraint using the FOREIGN KEY keyword when defining a table. This constraint ensures that the data in the foreign key column matches the data in the primary key column of the referenced table.

Unlocking the Power of Foreign Keys in MySQL

Foreign keys play an important role in creating a well-structured database that can maintain data integrity and provide efficient access to data across related tables.

1. A foreign key establishes a relationship between two tables in a MySQL database.
2. The foreign key is a column or a set of columns in one table that references the primary key of another table.
3. The foreign key helps ensure data integrity by enforcing referential integrity between related tables.
4. With a foreign key, you can create a link between the data in two tables, which allows you to retrieve and combine data from both tables with a single query.
5. Foreign keys can be created using the ALTER TABLE statement, either when creating a table or by modifying an existing table.

Understanding Foreign Key Constraints in MySQL

In MySQL, a foreign key constraint stops you from deleting the records of the parent row. Simply it means parent records cannot be deleted if the child records are present. A foreign key is to ensure data consistency across linked tables. It contains five different referential options, which specify what type of action should be taken when a user tries to delete or update a row related to another table. These referential options are,

1. CASCADE- When a row in the Master table is deleted or updated, the corresponding rows in the child table are also deleted or updated, respectively.
2. SET NULL- When a row in the Master table is deleted or updated, the corresponding values in the child table are set to NULL.
3. RESTRICT- This prevents the deletion or updation of a row in the Master table if there are corresponding rows in the child table.
4. SET DEFAULT- When a row in the Master table is deleted or updated, then the corresponding values in the child table are set to their default values.
5. NO ACTION- This option is similar to RESTRICT but does not generate an error message. Instead, the action is silently ignored.

Note

• Master table- The data of this table is not changed frequently.
• Child table- The data of this table changes frequently.

Let's understand this better by taking an example with the help of MySQL.

create database school;

Created a database and named it a school.

use school;

Here, use is a keyword in MySQL to use a database.

create table student (studentId int not null primary key auto_increment, 
studentName varchar(50), studentAddress varchar(100));
create table teacher (teacherId int not null primary key auto_increment, 
teacherName varchar(50), studentId int, 
foreign key (studentId) references student (studentId));

We then created a Master table named student and a child table named teacher. This student table will create a table with columns studentId, studentName, and studentAddress, where studentId is an integer that can not be null and will increment automatically and be designated as the primary key for this student table. And studentName and studentAddress are strings that can be null.

After this, the child table i.e. teacher table, creates a table with columns teacherId, which is defined as an integer that can not be null and will increment automatically teacherName is defined as a string that can have a null value, and studentId is taken as a reference from student table(Master table). This is done so we can establish a relationship between student and teacher.

​insert into student (studentName, studentAddress) value 
("ABC", "Jabalpur"), ("XYZ", "Noida");
insert into teacher (teacherName, studentId) 
value ("DEF", 1), ("EFG", 2) ;

In the above section, we insert values into student and teacher tables, respectively.

Select * from student;

Select * from teacher;

Selecting data from the student table and teacher table, respectively.

Now, trying to delete the records of a student (Master table).

Here, an attempt to delete a referenced parent row causes a foreign key constraint violation. Because the job of a foreign key is to ensure data consistency across linked tables. You are trying to delete a parent row referenced in another table.

Handling foreign key constraints in MySQL

The simplest way to solve this problem is to delete the foreign key from the child table. This will delete the relationship between the two tables.

Follow these steps to handle the foreign key constraints.

Syntax

ALTER TABLE tbl_name DROP FOREIGN KEY foreign_key_name;

Example

ALTER TABLE teacher DROP FOREIGN KEY teacher_ibfk_1;

After dropping the foreign key link with the master table, add this foreign in your child table with constraints like delete cascade.

1. Right-click on your child table e.g. teacher, then click the Alter table option.
2. After this, click on the foreign keys. Look at the right-side corner. An arrow button will be shown after clicking on that image will be shown.
3. In the foreign key options, Add cascade as shown below image.
4. Click on Apply. By this, changes will apply to your table.

Note. Cascade means when any entry is deleted from the parent row; then it will be deleted automatically from the child table.

Now, we can delete the record of a student whose id is 1.

After doing this, whenever you insert new values in your Master table and delete them from the Master table, they will also be removed from the child table.

Conclusion

Here I have discussed the solution to foreign key constraints. With the right implementation, foreign keys can greatly improve the efficiency and reliability of your database. I hope this will be useful for you. Thanks for reading this article.

FAQ's

Q 1- Can a foreign key have duplicate values?

A- The foreign key may contain duplicate values. And a table can have multiple foreign keys, which allows various relationships between the table and other tables in the database.

Q 2- Can a table have two foreign keys?

A- A table may have multiple foreign keys, and each foreign key can have a different parent table. And for this, cascading relationships between tables can be established using foreign keys.

Q 3- How to create a foreign key referencing a table in a different database in MySQL?

A- Yes, it is possible to create a foreign key that references a table in a different database in MySQL. However, some conditions must be true.

1. The user account used to create the foreign key must have the necessary privileges to access both databases.
2. The table in the other database must exist and must have a primary key or unique key that the foreign key can reference.

ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column_name)
REFERENCES other_database_name.other_table_name (referenced_column_name);

In the above query, table_name is the name of the table that will contain the foreign key, constraint_name defines a new constraint to the table, other_database is the name of the database containing the referenced table, other_table_name is the name of the referenced table, and referenced_column_name is the name of the column in the referenced table that the foreign key column is referencing.

Q 4- How to check foreign key constraints in MySQL?

A- We can check the foreign key constraints in MySQL by using the below syntax.

show create table table_name;

In the above query, table_name is the name of your table that contains a foreign key.