Unable to drop a foreign key

[rotten69]

I created a foreign key on a table just using this statement. When I wanted to drop it, phpMyAdmin didn't allow me to do so. Would anyone suggest a nice way of getting away with it?

ALTER TABLE `likes` ADD CONSTRAINT (fk_user_id) FOREIGN KEY REFERENCES `users` (user_id);

This line worked fine and created an index instead of creating a foreign key. And, I wanted to drop it and create a foreign key. Unfortunately, it didn't want to be dropped.

The message I got from phpMyAdmin is below:

SQL query:

ALTER TABLE `likes` DROP INDEX `fk_user_id`

MySQL said: Documentation
#1553 - Cannot drop index 'fk_user_id': needed in a foreign key constraint

Cheers,

[smantscheff]

You have to drop the foreign key relation before you can drop the index. Have a look at the output of

SHOW CREATE TABLE likes

It will show you the internal name of the foreign key constraint which you set up with your first statement. You have to drop this foreign key with

ALTER TABLE likes DROP FOREIGN KEY key_name

before you can drop the index on the foreign key field. InnoDB relies on indexes on all key columns in a foreign key constraint.

[rotten69]

Query went pretty smooth. Thanks, pal.