CODEWITHSUNDEEP
mysqlforeign-keysalter-table
xylar
xylar

Reputation: 7663

MySQL Drop foreign key Error 152

I am trying to drop a number of foreign keys using:

ALTER TABLE `table` DROP FOREIGN KEY `fk_table_users1` , DROP FOREIGN KEY `fk_table_accounts1` , DROP FOREIGN KEY `fk_table_data1` ;

but it returns the error:

Error on rename of './db/table' to './db/#sql2-179c-288289' (errno: 152)

I have run SHOW ENGINE INNODB STATUS which says:

120725 12:38:37 Error in dropping of a foreign key constraint of table db/table,
in SQL command
ALTER TABLE `table` DROP FOREIGN KEY `fk_table_users1` , DROP FOREIGN KEY `fk_table_accounts1` , DROP FOREIGN KEY `fk_table_data1` 
Cannot find a constraint with the given id fk_table_users1.

SHOW CREATE TABLE 'table' output:

CREATE TABLE `table` (
 `id` int(11) NOT NULL auto_increment,
 `data_id` int(11) NOT NULL,
 `account_id` int(11) NOT NULL,
 `status` enum('pending','complete') NOT NULL default 'pending',
 `created_at` datetime NOT NULL,
 `created_by` int(11) NOT NULL,
 PRIMARY KEY  (`id`),
 KEY `fk_orders_users1` (`created_by`),
 KEY `fk_orders_data1` (`data_id`),
 KEY `fk_orders_accounts1` (`account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

However when I look at the structure via phpmyadmin it lists the foreign key with the same name. Do I need to do something else before I can drop the foreign keys?

Upvotes: 14

Views: 24051

Answers (4)

Brett
Brett

Reputation: 21

You need to temporarily drop the constraint so that you can remove it.

SET FOREIGN_KEY_CHECKS=0;

and then turn them on again after you drop the foreign key:

SET FOREIGN_KEY_CHECKS=1;

Upvotes: 2

Nuryagdy Mustapayev
Nuryagdy Mustapayev

Reputation: 775

The index name and constraint name may not be same. You should delete constraint first using code: ALTER TABLE tablename DROP FOREIGN KEY constraintname

Upvotes: 0

user3065279
user3065279

Reputation: 11

first drop foreign key then delete column

alter table 'table name' drop foreign key 'constraint id ;

if you don't know constraint id create database dump in that constraint id is available in dump file ..

then delete column..

Upvotes: 0

jsist
jsist

Reputation: 5253

There are no foreign keys. Refer MySQL documentation which says

KEY is normally a synonym for INDEX.

So basically in table you have created indexes, not foreign keys. For Foreign Key info, Click here

Upvotes: 12

Related Questions