Pritam Banerjee Pritam Banerjee - 3 months ago 22
MySQL Question

Column is there, but when I try to delete it says no column in MYSQL? **Error Code: 1091. Can't DROP...**

I try to run the following query:

ALTER TABLE ORDER_DETAIL DROP foreign key USER_ID;


It says:

Error Code: 1091. Can't DROP 'USER_ID'; check that column/key exists


When I run:

ALTER TABLE ORDER_DETAIL DROP COLUMN USER_ID


It says:

Error Code: 1553. Cannot drop index 'USER_ID': needed in a foreign key constraint 0.098 sec


But when I run:

desc ORDER_DETAIL;


I get:

Field,Type,Null,Key,Default,Extra
ORDER_ID,int(11),NO,PRI,NULL,
USER_ID,int(11),NO,MUL,NULL,
ORDER_DATE,date,YES,,NULL,


Can anybody explain what is wrong here and how to fix it?

Answer

Since MySQL creates an index for the foreign keys so directly trying to drop the foreign key does not work. So the answers provided here does not work.

The right sql query that worked for me is this:

ALTER TABLE ORDER_DETAIL DROP FOREIGN KEY ORDER_DETAIL_ibfk_1;

The important thing to notice here is _ibfk_1

And only after that I could drop the column by using:

ALTER TABLE ORDER_DETAIL DROP COLUMN USER_ID;
Comments