CODEWITHSUNDEEP
mysqlsqldatabasedatabase-design
Eduan Bekker
Eduan Bekker

Reputation: 431

Drop Column with foreign key in MySQL

I have the following 2 tables:

CREATE TABLE `personal_info` (
 `p_id` int(11) NOT NULL AUTO_INCREMENT,
 `name` text NOT NULL,
 `initials` text NOT NULL,
 `surname` text NOT NULL,
 `home_lang` int(11) NOT NULL,
 PRIMARY KEY (`p_id`),
 KEY `home_lang` (`home_lang`),
 CONSTRAINT `personal_info_ibfk_1` FOREIGN KEY (`home_lang`) REFERENCES `language_list` (`ll_id`)
) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=latin1

CREATE TABLE `language_list` (
 `ll_id` int(11) NOT NULL AUTO_INCREMENT,
 `name` text NOT NULL,
 PRIMARY KEY (`ll_id`)
) ENGINE=InnoDB AUTO_INCREMENT=73 DEFAULT CHARSET=latin1

I am trying to remove a column from a table with the following:

ALTER TABLE `personal_info` DROP `home_lang`

But cannot do it since I recieve this error:

#1025 - Error on rename of '.\MyDB\#sql-112c_82' to '.\MyDB\personal_info' (errno: 150)

I have tried to first remove the index and then remove the column with this:

ALTER TABLE personal_info DROP INDEX home_lang

But then I get the following error:

#1553 - Cannot drop index 'home_lang': needed in a foreign key constraint

So I tried to drop the foreign key:

ALTER TABLE personal_info DROP FOREIGN KEY home_lang

But received this error:

#1025 - Error on rename of '.\MyDB\personal_info' to '.\MyDB\#sql2-112c-8d' (errno: 152)

I have also tried to first set all the values to null:

update personal_info set home_lang = null

But then received this error:

#1452 - Cannot add or update a child row: a foreign key constraint fails (`MyDB`.`personal_info`, CONSTRAINT `personal_info_ibfk_1` FOREIGN KEY (`home_lang`) REFERENCES `language_list` (`ll_id`))

And now I am stuck. I have tried a few things but just cannot get the column removed. I am not allowed to alter the DB in any way other than removing the column.

Upvotes: 24

Views: 65967

Answers (3)

Akshay Naik
Akshay Naik

Reputation: 565

Use this given below query to find the name of the foreign key.

SHOW CREATE TABLE forms_main;

Then once u got the key, execute drop foreign key command

alter TABLE `forms_main`
drop FOREIGN key `forms_main_ibfk_1`;

Then execute the drop column command 

ALTER TABLE `forms_main` DROP `company_id`;

Upvotes: 13

Hithaisri M K
Hithaisri M K

Reputation: 11

ALTER TABLE db_name.table_name DROP FOREIGN KEY foreign_key; ALTER TABLE test.exam DROP INDEX id ;

Upvotes: 1

Marc B
Marc B

Reputation: 360762

Your DROP FOREIGN KEY syntax is using the wrong key name. It's trying to drop your "plain" index on the home_lang field. It's NOT the foreign key itself.

CONSTRAINT `personal_info_ibfk_1` FOREIGN KEY (`home_lang`) REFERENCES `language_list` (`ll_id`)
            ^^^^^^^^^^^^^^^^^^^^^--- THIS is the name of the foreign key

Try:

ALTER TABLE personal_info DROP FOREIGN KEY `personal_info_ibfk_1`

Upvotes: 33

Related Questions