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')
Code Snippet Queries: sql
- How to create a new table from existing table in SQL
- Database connection using mySQLi with object-oriented
- Get data in random order with limit from table using mysql
- Insert multiple rows in a single MySQL query
- SQLSTATE: Warning: 1265 Data truncated for column 'visibility' at row 1
- 1553 Cannot drop index 'posts_user_id_foreign': needed in a foreign key constraint
- How to check column exists or not in table using MySQL
- How to retrieve data from two tables with one SQL statement
- Drop foreign id column (user_id) in MySQL
- Create column after a column in existing table using query in MySQL
- Change existing MySQL table column id to autoincrement
- SQL query to delete records older than 6 months
- Get create table Query from existing table in phpMyAdmin
- How to check the MySQL version