CODEWITHSUNDEEP
mysqlsql
r123
r123

Reputation: 618

unable to drop existing unique key because of foreign key constraint

I need to generate a Unique key by dropping the existing key on MySQL. My current version is MySQL 5.7

I dropped the existing key using the following query,

DROP INDEX `uk_bookid_bookname` ON Books;

where BookId is the foreign key.

Then,I added new unique key using the following query,

ALTER TABLE Books ADD UNIQUE uk_bookid_bookname (BookId, BookName);

I got the following error,

ERROR 1553 (HY000): Cannot drop index 'uk_bookid_bookname': needed in a foreign key constraint

I need to drop the existing key and then add a new unique key. But, it works vice-versa.

Upvotes: 1

Views: 6319

Answers (1)

Shah Hassan
Shah Hassan

Reputation: 155

You have to drop the foreign key. Foreign keys in MySQL automatically create an index on the table 

ALTER TABLE mytable DROP FOREIGN KEY mytable_ibfk_1 ;

then add another index key

Upvotes: 2

Related Questions