Issue:
#1553 – Cannot drop index ‘index_name’: needed in a foreign key constraint
An issue is generally faced when we are going to drop a unique index from a table. For example, here I have the table articles that have the following structure:
| Column | Data type | Specifications |
|---|---|---|
| id | int(11) | PRIMARY KEY |
| company_id | int(11) | NOT NULL, FOREIGN KEY to companies table |
| article_title | varchar(100) | NOT NULL |
| article_description | varchar(1000) | NOT NULL |
| created_date | datetime | NOT NULL |
Initially, I applied a unique constraint on column pair (company_id, title) so that one company should not have duplicate article titles. The title should be unique within one company.
Later on, I feel, a unique constraint is not required. The title can be the same. So, I run the following query on phpmyadmin:
ALTER TABLE articles DROP INDEX 'article_title_unique';
article_title_unique is an index name given to a columns-pair (company_id, title).
But it doesn’t work & gives me an error:
#1553 - Cannot drop index 'article_title_unique': needed in a foreign key constraint
A solution to this issue:
It gives me an error because company_id is a foreign key in this columns-pair. If I would have made a normal column unique, then it would have easily been dropped with ALTER … DROP INDEX statement. But in my case, the unique key contains a foreign key, and it creates a problem for dropping an index.
I have done the following things to overcome this problem.
- Remove foreign key constraints for the company_id column.
- Then run ALTER … DROP INDEX statement to drop the unique index and it is dropped successfully.
Means, solution in simple terms is: first remove the foreign key constraint on column(s) under unique constraint and then execute ALTER … DROP INDEX query to drop the unique index.
Later on, I again add a foreign key constraint on the company_id column so that functioning remains smooth as before.