Skip to main content

Cannot drop index 'fk_role_id': needed in a foreign key constraint

If you are getting an error Cannot drop index 'fk_role_id': needed in a foreign key constraint, it means you are trying to drop the index without dropping the foreign key. To drop the index you have to drop the foreign key first. After that, you can drop the index.

  • Drop constraint in MySQL

    // Drop constraint in MySQL
    ALTER TABLE role_user DROP FOREIGN KEY fk_role_id; 
    ALTER TABLE role_user DROP INDEX fk_role_id;
    

    No MySQL query exists to drop a constraint. Foreign keys in MySQL automatically create an index on the table. To drop the index ‘fk_role_id’ you have to first drop foreign key, after dropping foreign key you can run a drop query on the table in MySQL to drop the index.

    You can run the below SQL query to check constraint exists or not. You have to just change the name of constraint instead of 'fk_role_id'.

    SELECT `TABLE_SCHEMA`, `TABLE_NAME` FROM `information_schema`.`KEY_COLUMN_USAGE` WHERE `CONSTRAINT_NAME` IN ('fk_role_id')

     

  • Back to code snippet queries related sql