Cannot drop index {index_name}: needed in a foreign key constraint

Question

I want to drop an index, but I can't because is used in another table, but I can't find where

ALTER TABLE t_course DROP INDEX user_id

Is there a way to know where it is used ?

check the indexes with the data base management tool you use. They have both the local field and the table and field they referencing — Juan
– Juan, Commented Sep 21, 2018 at 13:27

dustytrash · Accepted Answer · 2018-09-21 13:28:25Z

2

To find the other table that the constraints reference:

select COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_COLUMN_NAME, REFERENCED_TABLE_NAME
from information_schema.KEY_COLUMN_USAGE
where TABLE_NAME = 't_course';

Take a look at the REFERENCED_TABLE_NAME returned from the above query.

answered Sep 21, 2018 at 13:28

dustytrash

1,5901 gold badge11 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Fuad All Over a year ago

i am getting same error and this query returns null REFERENCED_COLUMN_NAME, and REFERENCED_TABLE_NAME

Collectives™ on Stack Overflow

Cannot drop index {index_name}: needed in a foreign key constraint

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related