EDUCBA

Home Data Science Data Science Tutorials MySQL Tutorial MySQL Drop Foreign Key

MySQL Drop Foreign Key

Aanchal Sharma
Article byAanchal Sharma
Priya Pedamkar
Reviewed byRavi Rathore

Updated May 26, 2023

MySQL Drop Foreign Key

 

 

Definition of MySQL Drop Foreign Key

Let’s start by discussing what a Foreign Key is in MySQL. In MySQL, with tables and databases, we also involve certain constraints to specify some rules for the table data and ensure consistency and accuracy. Therefore, A foreign key in MySQL is a constraint that restricts data types and establishes a relationship between two tables. We define a foreign key as a field or group of fields within a table that identifies a record or row in another table. It references the primary key field in the related table. When creating a table, we include the foreign key constraint name to reference the primary keys of other tables. Now, MySQL DROP foreign key constraint is a mysql query command to remove the foreign key present in the column of a particular table using the ALTER TABLE statement query along with the DROP keyword.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Syntax

Let us discuss the syntax code to perform the MySQL Drop Foreign Key query in the table written below:

ALTER TABLE TableName DROP FOREIGN KEY ConstraintName;

Let’s explain the terms in the syntax as follows:

The statement ALTER TABLE is used for the Drop Foreign Key query so that the foreign key created in the table can be modified, and the table will be updated. After this ALTER TABLE keywords, we will specify the table name from which the foreign key will be dropped. Next, we must state the constraint name after the keywords drop the foreign key in the above syntax. This constraint name defines the name of the foreign key specified constraint added while creating the table.

You can obtain the created constraint name of the particular table by the following command:

SHOW CREATE TABLE TableName;

Provide the table name here to view the result.

After dropping the Foreign Key, you can ensure this by viewing the structure of that table:

SHOW CREATE TABLE TableName;

How to Drop Foreign Key in MySQL?

  • MySQL supports foreign keys, which enable the cross-referencing of associated data across tables. Additionally, foreign key constraints ensure the consistency of the related data. The relationship of the foreign key contains a parent table with initial column values and a child table with column values that reference the parent table column values. Thus, we state the foreign key on the child table.
  • Here is a vital syntax that defines a foreign key constraint using the statements CREATE TABLE or ALTER TABLE shown below:
CONSTRAINT (symbol) FOREIGN KEY IndexName (Column1, Column2, …..)
REFERENCES TableName (Column1, Column2, …)
{ON DELETE Ref_option}
{ON UPDATE ref_option}
  • Here, the ref_option denotes five reference options in MySQL as: SET DEFAULT | RESTRICT | SET NULL |CASCADE | NO ACTION. Whereas, only three actions as: SET NULL | RESTRICT | CASCADE, are entirely supported by MySQL.
  • Also, if the clauses – ON DELETE and ON UPDATE are not specified, then MySQL’s default action will be RESTRICT option.
  • Once a MySQL Foreign Key is created on a table, you may want to remove the foreign key from that table; then, we need to implement the ALTER TABLE query:

Suppose you have an employee table in the database, and there is a column that references the foreign key of another table, say orders. The foreign key constraint name is “fk_orderid,” where “orderid” is a field in the orders table defined as the primary key and functions as a foreign key in the employee’s table. Now, if you wish to drop this foreign key constraint, then we can do so by the following ALTER TABLE query along with the DROP keyword:

ALTER TABLE Employees DROP CONSTRAINT fk_orderid;

When you execute the above query, the constraint name “fk_orderid” is removed.

Examples of MySQL Drop Foreign Key

Let us look through examples of how to understand the MySQL Drop Foreign Key command better and illustrate the ways to first create the Foreign key in a table and then perform the drop query for the Foreign key in the server as follows:

We will build up a table named Books with the Primary key column as Book_Id, which will be the parent table by the following statement:

CREATE TABLE Books (Book_Id INT PRIMARY KEY AUTO_INCREMENT, Book_Name VARCHAR(255) NOT NULL, Language VARCHAR(50) NOT NULL, Price INT NOT NULL) ENGINE = InnoDB;
SELECT * FROM 'books'

Output:

MySQL Drop Foreign Key-1.1

Again, we will create another table as a child table that refers to the column values of the parent table. We will define it as a Foreign key constraint.

CREATE TABLE Orders (Order_ID INT PRIMARY KEY AUTO_INCREMENT, Book_Id INT NOT NULL, Order_Name VARCHAR(255) NOT NULL, Order_Quantity INT NOT NULL, Order_Price INT NOT NULL, CONSTRAINT fk_order_book_id FOREIGN KEY(Book_Id) REFERENCES books (Book_Id)) ENGINE = InnoDB;
SELECT * FROM 'orders'

Output:

MySQL Drop Foreign Key-1.2

Using the CREATE TABLE query, we have also created the Foreign key constraint on the Orders table as fk_order_book_id, which will establish a connection between the Book_Id field in the Orders table and the Book_Id column present in the parent table Books.

Suppose you want to view the Foreign key constraint as fk_order_book_id created for the Orders table. Then, you can execute the command below:

SHOW CREATE TABLE Orders

Output:

Output-1.3

Now, due to any reason, if the user wants to delete the Foreign key constraint created on the Orders table, then we need to run the ALTER TABLE command written as follows:

ALTER TABLE Orders DROP FOREIGN KEY fk_order_book_id

When you execute the command, a warning message will prompt you to confirm whether you want to proceed with the command or not. You should click “OK” to proceed, and then the query will be executed.

To determine if the Foreign key constraint named “fk_order_book_id” created for the Orders table has been dropped or not, you can execute the following command:

SHOW CREATE TABLE Orders

Output:

output-1.4

As you can see in the result above that the foreign key constraint created on the Orders Table that referenced the parent table Book_Id column no longer exists in the table.

Conclusion

Since Foreign Key is a table column or collection of different columns in a table that associates with a table column or collection of columns in another table, it helps to place constraints on records in the linked tables, which maintains referential integrity in MySQL. The MySQL Drop Foreign Key statement query eliminates the Foreign Key constraint existing in a column of a specific table using the ALTER TABLE command.

Recommended Articles

We hope that this EDUCBA information on “MySQL Drop Foreign Key” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. MySQL NOW
  2. Examples of MySQL Hour()
  3. MySQL Update Join
  4. MySQL Cardinality