Unable to drop a foreign key column in MySql

Question

I added a column in a table (query below)

ALTER TABLE `acct_doc_item_data`
ADD fk_job_id INT(11), 
ADD FOREIGN KEY (fk_job_id) REFERENCES `job`(`job_id`);

Now I want to drop this column (fk_job_id). I tried these queries but they are giving error.

ALTER TABLE `acct_doc_item_data` DROP FOREIGN KEY `fk_job_id`;
ALTER TABLE `acct_doc_item_data` DROP COLUMN `fk_job_id`;

1st Alter statement gives error as - Can't DROP 'fk_job_id'; check that column/key exists (But the column exists).

2nd Alter statement gives error as - Cannot drop index 'fk_job_id': needed in a foreign key constraint

you need to drop the name of the index, `ALTER TABLE acct_doc_item_data DROP FOREIGN KEY ;`, you cannot use the column name when dropping foreign key, only when dropping the column. — Napoli, Mar 13 '18 at 07:10
You did not provide a foreign key name when you added the foreign key , if memory serves mysql will allocate one for you try https://stackoverflow.com/questions/201621/how-do-i-see-all-foreign-keys-to-a-table-or-column/18825955 to identify the name. — P.Salmon, Mar 13 '18 at 07:15

score 3 · Accepted Answer · answered Mar 13 '18 at 07:15

first drop the foreign key constraint then drop the column

eg:

alter table table_name drop constraint constraint_name


alter table table_name drop column column_name

to get constraint name use this

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME='YourTableName';

score 2 · Answer 2 · answered Nov 28 '19 at 10:22

In my case(phpmyadmin and Mysql InnoDB) You cannot just do:

alter table table_name drop constraint constraint_name

becouse it gives "Acces denied" for root "to database 'information_schema' "

But I could go to Structure tab -> Relation view and from there you can drop desired fk

2 Answers2