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

Question

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 1

You are trying to drop the foreign key. For that what you need to give is the name of the foreign key. To find the name of the foreign key, do SHOW CREATE TABLE. Then use that in the ALTER TABLE

ALTER TABLE ORDER_DETAIL DROP foreign key key_name_from_show_create;

If it's really the column you want to drop

ALTER TABLE ORDER_DETAIL DROP COLUMN USER_ID;