Column is there, but when I try to delete it says

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

Use in below steps 

1) drop foreign constraint

ALTER TABLE ORDER_DETAIL 
DROP FOREIGN KEY fk_order_detail // name of your foreign key

2) then run below command to drop column

ALTER TABLE ORDER_DETAIL DROP COLUMN USER_ID; 

Answer 2

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;

Answer 3

If you want to drop Foreign Key constraint then use constraint name:

alter table ORDER_DETAIL drop constraint USER_ID_CONSTRAINT

here USER_ID_CONSTRAINT is the constraint name.

If you want to drop column then use this:

alter table ORDER_DETAIL drop column USER_ID

To get all constraint name you can use this:

select COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_COLUMN_NAME,  REFERENCED_TABLE_NAME
from information_schema.KEY_COLUMN_USAGE
where TABLE_NAME = 'table to be checked';

Answer 4

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;