Mysql | #1553 - Cannot drop Index

Author - Webner
23.09.2020
|
0 Comments
||

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.

  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.

Leave a Reply

Your email address will not be published. Required fields are marked *