2

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,

Edited by rotten69: n/a

2
Contributors
2
Replies
7
Views
5 Years
Discussion Span
Last Post by rotten69
1

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.

Votes + Comments
really helpul
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.