How to Drop Foreign Key in MySQL

The foreign key is the key that links two or more tables together and forms a meaningful link between them.

This key refers to the primary key in the child table and acts as a foreign key in the parent table. It is often called a reference key constraint.

Drop Foreign Key in MySQL

The collected fact about the foreign key is that since it acts as a primary key, there can be no more than one entry in the table, and the record is unique concerning the primary key.

The foreign key is a constraint that restricts the user from directly deleting the linked records between tables which helps in preventing the user action and leads to data security.

The table where the foreign key resides is called the child table, and the one from which the key comes is called the parent table. The concept of linking tables only comes in the Relational Database Management Systems.

The benefits of foreign key constraints in MySQL are below.

1. The constraint enforces referential integrity and hence consistency across the tables.
2. Using constraints such as ON DELETE CASCADE and ON UPDATE CONSTRAINTS helps maintain the consistent behavior of tables across the database.
3. It eventually increases the performance as keeping constraints on interlinked tables decreases the overhead of remembering the tables and is used to detect the linked tables.
4. It increases performance by performing better joins and retrieving results faster as tables are linked already.
5. When a record in the parent table gets deleted before the foreign key, the query fails and does not allow the records to delete. It shows the linkage between tables and asks to delete the records from the child table instead of the parent first.

Below is the screenshot for the same.

To drop a foreign key, one should first create tables and generate foreign keys in the tables. Below is the command to create the foreign key constraint in the tables defined.

create table student(id varchar(255), name varchar(255), dob date, deptId varchar(255), primary key(id));
create table department(dept_id varchar(255), id varchar(255), dept_name varchar(255), constraint fk_id foreign key(id) references student(id));

First, the above query creates two tables: student and department. The table is in the form parent-child relationship where the attribute id in the student table acts as a foreign key id in the department table.

Screenshots attached:

When records in the table get inserted, and when trying to manipulate the inserted record with a delete statement, it will populate the error shown above. To fix the populating issue, drop the foreign key constraint and perform the delete action.

Now the syntax to drop the above-created constraint in MySQL is:

Alter table table_name drop constraint constraint_name;

The above command uses the alter command to change the table schema and the drop keyword to drop the constraint applied to the schema. Alter table is a Data Definition Language also known as the DDL command and helps create database and table schema.

Alter table department drop constraint fk_id;

The screenshot of the delete statement when the foreign key constraint is removed is below.