Unable to delete existing unique key due to foreign key constraint

Question

I need to create a Unique key by discarding the existing key in MySQL. My current version of MySQL is 5.7

I deleted the existing key using the following query,

DROP INDEX `uk_bookid_bookname` ON Books;    

where BookId is the foreign key.

Then I added a 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): Unable to delete index 'uk_bookid_bookname': required in foreign key constraint

I need to drop the existing key and then add a new unique key. But, it works the other way around.

Answer 1

You need to refuse the foreign key. Foreign keys in MySQL automatically create an index on a table

ALTER TABLE mytable DROP FOREIGN KEY mytable_ibfk_1 ;