Mysql (Relational DB management system) | #1553 — Cannot drop Index

Mysql (Relational DB management system) | #1553 — Cannot drop Index

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:

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.

1. Remove foreign key constraints for the company_id column.
2. 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.

Webner Solutions is a Software Development company focused on developing Insurance Agency Management Systems, Learning Management Systems and Salesforce apps. Contact us at dev@webners.com for your Insurance, eLearning and Salesforce applications.

Originally published at https://blog.webnersolutions.com on September 23, 2020.