Drop Foreign Key MySQL via Command Line

This article is specifically describing on how to drop foreign key exist in a table within MySQL Database Server. The main goal is for dropping a single column within a table. But the problem is dropping the column is failing since the column dropped is associated with a foreign key defined. It has a reference with the primary key defined in another table. So, in order to drop the column, the first thing which is needed to be achieved is by dropping the foreign key defined. Below is the steps taken to drop the foreign key and finally dropping the column.

First of all, the table which is going to be altered by dropping one column on it can be described and shown as follows :

mysql> select * from db;
+-------+------------+---------+--------+-----------+
| id_db | name       | version | id_app | id_server |
+-------+------------+---------+--------+-----------+
|     9 | PostgreSQL | 9.3     |      0 |         0 |
|    11 | MySQL      | 5.6.27  |      0 |         0 |
+-------+------------+---------+--------+-----------+
2 rows in set (0,00 sec)
mysql>

It has the following structure :

mysql> desc db;
+-----------+--------------+------+-----+---------+---------------+
| Field     | Type         | Null | Key | Default | Extra         |
+-----------+--------------+------+-----+---------+---------------+
| id_db     | int(11)      | NO   | PRI | NULL    | auto_increment|
| name      | varchar(255) | YES  |     | NULL    |               |
| version   | varchar(255) | YES  |     | NULL    |               |
| id_app    | int(11)      | NO   |     | NULL    |               |
| id_server | int(255)     | NO   | MUL | NULL    |               |
+-----------+--------------+------+-----+---------+---------------+
5 rows in set (0,01 sec)
mysql> 

The first column which is going to be dropped is the ‘id_app’ column. Basically, there is no problem at all dropping the column. It can be shown in the following output query execution command :

mysql> alter table db drop column id_app;
Query OK, 0 rows affected (0,40 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>

But the problem rise when the column dropped is the ‘id_server’ column. It can be shown as follows :

mysql> alter table db drop column id_server;
ERROR 1553 (HY000): Cannot drop index 'id_server': needed in a foreign key constraint
mysql> set foreign_key_checks=0;
Query OK, 0 rows affected (0,00 sec)
mysql>

There is a specific error pointed out that the ‘id_server’ column cannot be dropped since it is needed in a foreign key constraint. So, the column can be erased. Another way for dropping the column is also selected by ignoring the process of checking foreign key with the execution of ‘set foreign_key_checks=0’ query, but it doesn’t affect the process at all. So, one way or another, the foreign key associated with that column must be erased first. To be able to see the foreign key in the table, just execute the following command :

mysql> show create table db;
+-------+--------------------------------------------------------------+
| Table | Create Table                                                 |
+-------+--------------------------------------------------------------+
| db | CREATE TABLE `db` (
`id_db` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`version` varchar(255) DEFAULT NULL,
`id_server` int(11) NOT NULL,
PRIMARY KEY (`id_db`),
KEY `id_server` (`id_server`),
CONSTRAINT `db_ibfk_1` FOREIGN KEY (`id_server`) REFERENCES `server` (`id_server`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1               |
+-------+--------------------------------------------------------------+
1 row in set (0,00 sec)

As we can see, by executing ‘show create table db_name’ which is aiming to display the script used for generating the table, the foreign key which exist in it can also be presented. The foreign key name based on the above output which is associated with the column ‘id_server’ is ‘db_ibfk_1’. So, based on the information given on the above output, just drop the foreign key by executing the following query :

mysql> alter table db drop foreign key db_ibfk_1;
Query OK, 0 rows affected (0,03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>

After successfully dropping the associated foreign key, the process can be continued by dropping the actual column intended as shown the following query execution :

mysql> alter table db drop column id_server;
Query OK, 0 rows affected (0,21 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>