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