| Bug #14347 | Can't remove FOREIGN KEY | ||
|---|---|---|---|
| Submitted: | 26 Oct 2005 18:53 | Modified: | 28 Jan 2009 15:43 |
| Reporter: | Vadim Tkachenko | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Parser | Severity: | S2 (Serious) |
| Version: | 5.0.15, 5.0.74, 5.1.30, 6.0.9 | OS: | Linux (Linux Suse 10, Windows) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | foreign key, innodb | ||
[26 Oct 2005 19:54]
MySQL Verification Team
miguel@hegel:~/dbs/5.0> bin/mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.16-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE `CUSTOMERS` (
->
-> `CUSTOMERID` int(11) NOT NULL auto_increment,
->
-> `FIRSTNAME` varchar(50) NOT NULL,
->
-> `LASTNAME` varchar(50) NOT NULL,
->
-> `ADDRESS1` varchar(50) NOT NULL,
->
-> `ADDRESS2` varchar(50) default NULL,
->
-> `CITY` varchar(50) NOT NULL,
->
-> `STATE` varchar(50) default NULL,
->
-> `ZIP` int(11) default NULL,
->
-> `COUNTRY` varchar(50) NOT NULL,
->
-> `REGION` tinyint(4) NOT NULL,
->
-> `EMAIL` varchar(50) default NULL,
->
-> `PHONE` varchar(50) default NULL,
->
-> `CREDITCARDTYPE` int(11) NOT NULL,
->
-> `CREDITCARD` varchar(50) NOT NULL,
->
-> `CREDITCARDEXPIRATION` varchar(50) NOT NULL,
->
-> `USERNAME` varchar(50) NOT NULL,
->
-> `PASSWORD` varchar(50) NOT NULL,
->
-> `AGE` tinyint(4) default NULL,
->
-> `INCOME` int(11) default NULL,
->
-> `GENDER` varchar(1) default NULL,
->
-> PRIMARY KEY (`CUSTOMERID`),
->
-> UNIQUE KEY `IX_CUST_USERNAME` (`USERNAME`)
->
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.06 sec)
mysql>
mysql> CREATE TABLE `ORDERS` (
->
-> `ORDERID` int(11) NOT NULL auto_increment,
->
-> `ORDERDATE` date NOT NULL,
->
-> `CUSTOMERID` int(11) default NULL,
->
-> `NETAMOUNT` decimal(12,2) NOT NULL,
->
-> `TAX` decimal(12,2) NOT NULL,
->
-> `TOTALAMOUNT` decimal(12,2) NOT NULL,
->
-> PRIMARY KEY (`ORDERID`),
->
-> KEY `IX_ORDER_CUSTID` (`CUSTOMERID`),
->
-> CONSTRAINT `FK_CUSTOMERID` FOREIGN KEY (`CUSTOMERID`) REFERENCES `CUSTOMERS`
-> (`CUSTOMERID`) ON DELETE SET NULL
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.06 sec)
mysql> INSERT INTO `CUSTOMERS` VALUES (1,'VKUUXF','ITHOMQJNYX','4608499546 DellWay',
-> '','QSDPAGD','SD',24101,'US',1,'ITHOMQJNYX@dell.com',
-> '4608499546',1,'1979279217775911','2012/03','user1','password',55,100000,'M'),(2,
-> 'HQNMZH','UNUKXHJVXB','5119315633 Dell Way','',
-> 'YNCERXJ','AZ',11802,'US',1,'UNUKXHJVXB@dell.com','5119315633',1,'3144519586581737',
-> '2012/11','user2','password',80,40000,'M');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> INSERT INTO `ORDERS` VALUES (10677,'2004-11-10',2,'5.08','0.42','5.50');
Query OK, 1 row affected (0.01 sec)
mysql> ALTER TABLE ORDERS DROP FOREIGN KEY CUSTOMERID;
ERROR 1025 (HY000): Error on rename of './test/ORDERS' to './test/#sql2-1d16-2' (errno: 152)
mysql> exit
Bye
miguel@hegel:~/dbs/5.0> bin/perror 152
MySQL error code 152: Cannot delete a parent row
[26 Oct 2005 20:58]
Heikki Tuuri
Hi! You must use the name of the CONSTRAINT in the drop statement. Not the name of the INDEX. I admit this is confusing. We should replace the non-standard SQL with a more standard statement: ... DROP CONSTRAINT ... Regards, Heikki
[26 Oct 2005 21:04]
Vadim Tkachenko
Then I'd expect more intuitive error like "syntax error", but not "cannot delete parent row"
[17 Oct 2006 9:25]
Stefan Lenk
Dropping the foreign key constraint by constraint name does not work either: mysql> create table ref (ref_id int unsigned not null, something varchar(30), primary key(ref_id)); Query OK, 0 rows affected (0.27 sec) mysql> create table test (test_id int unsigned not null auto_increment, ref_id int unsigned not null, something_else varchar(30), primary key(test_id), foreign key(ref_id) references ref(ref_ID)); Query OK, 0 rows affected (0.11 sec) i try to remove the foreign key constraint by it's column name (ok, i know, that wont work): mysql> alter table test drop foreign key ref_ID; ERROR 1025 (HY000): Error on rename of '.\eltas\test' to '.\eltas\#sql2-704-2' (errno: 152) now, i try to remove the constraint by it's constraint name: mysql> alter table test drop foreign key FKref_ID; ERROR 1025 (HY000): Error on rename of '.\eltas\test' to '.\eltas\#sql2-704-2' (errno: 152) mysql> select version(); +---------------------+ | version() | +---------------------+ | 5.0.21-community-nt | +---------------------+ 1 row in set (0.00 sec)
[1 Nov 2006 13:32]
Timour Katchaounov
It looks like the bug has been fixed for tables with a simple
one-column key, but the problem still exists for composite keys.
The following example fails with the same error as above:
CREATE TABLE `t2` (
`ORG_ID` int(11) NOT NULL default '0',
`CUSTOMER_ID` varchar(25) NOT NULL default '',
PRIMARY KEY (`ORG_ID`,`CUSTOMER_ID`)
) ENGINE=InnoDB;
CREATE TABLE `t1` (
`ORG_ID` int(11) NOT NULL default '0',
`BILL_ID` int(11) NOT NULL default '0',
`CUSTOMER_ID` varchar(25) default NULL,
PRIMARY KEY (`ORG_ID`,`BILL_ID`),
KEY `T1_SK04` (`ORG_ID`,`CUSTOMER_ID`),
CONSTRAINT `T1_FK04` FOREIGN KEY
(`ORG_ID`, `CUSTOMER_ID`) REFERENCES `t2`
(`ORG_ID`, `CUSTOMER_ID`)
) ENGINE=InnoDB;
alter table t1 drop foreign key t1_fk04;
ERROR 1025 (HY000): Error on rename of './olsw1/t1' to './olsw1/#sql2-59f2-1' (errno: 152)
[8 Nov 2006 14:30]
Heikki Tuuri
Hi!
You should use the constraint name, and in the case where you defined it. Then DROP FOREIGN KEY works.
Marking this as not a bug.
Regards,
Heikki
mysql> CREATE TABLE `t2` (
-> `ORG_ID` int(11) NOT NULL default '0',
-> `CUSTOMER_ID` varchar(25) NOT NULL default '',
-> PRIMARY KEY (`ORG_ID`,`CUSTOMER_ID`)
-> ) ENGINE=InnoDB;
CREATE TABLE `t1` (
`ORG_ID` int(11) NOT NULL default '0',
`BILL_ID` int(11) NOT NULL default '0',
`CUSTOMER_ID` varchar(25) default NULL,
PRIMARY KEY (`ORG_ID`,`BILL_ID`),
KEY `T1_SK04` (`ORG_ID`,`CUSTOMER_ID`),
CONSTRAINT `T1_FK04` FOREIGN KEY
(`ORG_ID`, `CUSTOMER_ID`) REFERENCES `t2`
(`ORG_ID`, `CUSTOMER_ID`)
) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> CREATE TABLE `t1` (
-> `ORG_ID` int(11) NOT NULL default '0',
-> `BILL_ID` int(11) NOT NULL default '0',
-> `CUSTOMER_ID` varchar(25) default NULL,
-> PRIMARY KEY (`ORG_ID`,`BILL_ID`),
-> KEY `T1_SK04` (`ORG_ID`,`CUSTOMER_ID`),
-> CONSTRAINT `T1_FK04` FOREIGN KEY
-> (`ORG_ID`, `CUSTOMER_ID`) REFERENCES `t2`
-> (`ORG_ID`, `CUSTOMER_ID`)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.05 sec)
mysql> show create table t1;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`ORG_ID` int(11) NOT NULL default '0',
`BILL_ID` int(11) NOT NULL default '0',
`CUSTOMER_ID` varchar(25) default NULL,
PRIMARY KEY (`ORG_ID`,`BILL_ID`),
KEY `T1_SK04` (`ORG_ID`,`CUSTOMER_ID`),
CONSTRAINT `T1_FK04` FOREIGN KEY (`ORG_ID`, `CUSTOMER_ID`) REFERENCES `t2` (`ORG_ID`, `CUSTOMER_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter table t1 drop foreign key T1_FK04;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
[21 Dec 2006 15:36]
John Lame
I'm seeing a similar issue when trying to drop a foreign key column. Using the same case doesn't seem to help. CREATE TABLE test1 ( b1 smallint NOT NULL, PRIMARY KEY (b1) ); CREATE TABLE test2 ( c1 smallint NOT NULL, c2 smallint NOT NULL, PRIMARY KEY (c1), CONSTRAINT FK1 FOREIGN KEY (c2) REFERENCES test1 (b1) ); ALTER TABLE test2 drop column c2; 10:05:12 [ALTER - 0 row(s), 0.234 secs] [Error Code: 1025, SQL State: HY000] Error on rename of '.\qovia\#sql-e64_3' to '.\qovia\test2' (errno: 150) ... 1 statement(s) executed, 0 row(s) affected, database exec time 0.234 sec [0 successful, 0 warnings, 1 errors]
[21 Dec 2006 15:50]
Heikki Tuuri
Hi! Hmm... the workaround probably is to drop the foreign key constraint first, and only after that the column. I wonder if is a good idea to let DROP COLUMN 'cascade' to constraints, as one may then accidentally change the database logic? Regards, Heikki
[28 Aug 2007 11:55]
Shaik Abid
iam trying to change the data type of a column which is a primary key int to varchar. first i disables the constraint that time i got an error Table storage engine for 'table ' doesn't have this option
[7 Dec 2007 22:27]
Andres March
Why is this marked as not a bug. I can't write an automated script to drop a column without knowing the constraint name. Surely the drop column should cascade or allow cascade semantics to remove dependent db objects like constraints. In fact, it already does this for indexes.
[28 Jan 2009 15:43]
Valeriy Kravchuk
This is still a bug, repeatable in 5.0.74:
C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 5.0.74-enterprise-gpl-nt-log MySQL Enterprise Server - Pro Editi
on (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE IF NOT EXISTS CollectionInfo (
-> CollectionName VARCHAR(255) NOT NULL PRIMARY KEY
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.20 sec)
mysql> CREATE TABLE IF NOT EXISTS CollectionRequestHandler (
-> ID INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
-> CollectionName VARCHAR(255),
-> CONSTRAINT `crh_cn` FOREIGN KEY (CollectionName) REFERENCES
-> CollectionInfo(CollectionName) ON DELETE CASCADE
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.25 sec)
mysql> ALTER TABLE CollectionInfo DROP FOREIGN KEY `crh_cn`;
ERROR 1025 (HY000): Error on rename of '.\test\collectioninfo' to '.\test\#sql2-
f6c-1a' (errno: 152)
[28 Jan 2009 15:47]
Valeriy Kravchuk
Bug #42416 was marked as a duplicate of this one.
[12 May 2009 13:46]
Matthew Sgarlata
This prevents columns and tables referenced in foreign keys from being deleted. Ouch!
[15 Jun 2009 16:54]
Alex Sherwin
In response to Valeriy Kravchuk, You're trying to drop the FK from the wrong table, you added the FK to CollectionRequestHandler, not CollectionInfo Your test case works fine when using the right table: CREATE TABLE IF NOT EXISTS CollectionInfo (CollectionName VARCHAR(255) NOT NULL PRIMARY KEY) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS CollectionRequestHandler ( ID INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, CollectionName VARCHAR(255), CONSTRAINT `crh_cn` FOREIGN KEY (CollectionName) REFERENCES CollectionInfo(CollectionName) ON DELETE CASCADE ) ENGINE=InnoDB; ALTER TABLE CollectionRequestHandler DROP FOREIGN KEY `crh_cn`;
[15 Jun 2009 17:21]
Alex Sherwin
In response to Timour Katchaounov,
It appears that your test case doesn't work because you have the wrong FK name in your alter table statement. The FK appears to be case-sensitive, your test case works by using the correct FK name:
CREATE TABLE `t2` (
`ORG_ID` int(11) NOT NULL default '0',
`CUSTOMER_ID` varchar(25) NOT NULL default '',
PRIMARY KEY (`ORG_ID`,`CUSTOMER_ID`)
) ENGINE=InnoDB;
CREATE TABLE `t1` (
`ORG_ID` int(11) NOT NULL default '0',
`BILL_ID` int(11) NOT NULL default '0',
`CUSTOMER_ID` varchar(25) default NULL,
PRIMARY KEY (`ORG_ID`,`BILL_ID`),
KEY `T1_SK04` (`ORG_ID`,`CUSTOMER_ID`),
CONSTRAINT `T1_FK04` FOREIGN KEY
(`ORG_ID`, `CUSTOMER_ID`) REFERENCES `t2`
(`ORG_ID`, `CUSTOMER_ID`)
) ENGINE=InnoDB;
alter table t1 drop foreign key T1_FK04;
[21 Aug 2009 20:56]
michele braidotti
I found that in a table like this: CREATE TABLE `table3` ( `keya` varchar(25) NOT NULL, `keyb` varchar(12) NOT NULL, `keyc` varchar(12) NOT NULL, `t2_id` int(11) default NULL, `t2_weakkey` int(3) default NULL, PRIMARY KEY (`keya`,`keyb`,`keyc`), KEY `new_fk_constraint1` (`t2_id`,`t2_weakkey`), CONSTRAINT `new_fk_constraint1` FOREIGN KEY (`t2_id`, `t2_weakkey`) REFERENCES `table2` (`t1_id`, `weakkey`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `new_fk_constraint` FOREIGN KEY (`keya`, `keyb`) REFERENCES `tableA` (`keya`, `keyb`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB this will fail: ALTER TABLE `table3` DROP INDEX `new_fk_constraint1`; but this will work: ALTER TABLE `testdb`.`table3` DROP FOREIGN KEY `new_fk_constraint1`;
[1 Sep 2009 17:03]
Heikki Tuuri
MySQL should add to their parser DROP CONSTRAINT ...
[1 Sep 2009 17:35]
Valeriy Kravchuk
Indeed, my test case dated [28 Jan 16:43] was wrong. It works with correct table name:
mysql> CREATE TABLE IF NOT EXISTS CollectionInfo (CollectionName VARCHAR(255) NOT NULL PRIMARY KEY) ENGINE=InnoDB;
Query OK, 0 rows affected (0.05 sec)
mysql> CREATE TABLE IF NOT EXISTS CollectionRequestHandler (
-> ID INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
-> CollectionName VARCHAR(255),
-> CONSTRAINT `crh_cn` FOREIGN KEY (CollectionName) REFERENCES
-> CollectionInfo(CollectionName) ON DELETE CASCADE
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.06 sec)
mysql> ALTER TABLE CollectionRequestHandler DROP FOREIGN KEY `crh_cn`;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
[1 Sep 2009 17:37]
Valeriy Kravchuk
This: "MySQL should add to their parser DROP CONSTRAINT ..." is still a reasonable feature request.
[23 Dec 2010 12:32]
feida kila
I've had the same issue, but in my case was caused because i refered the table without the database name: table_name instead of database_name.table_name so the first did not work but the second did 1) mysql> alter table usuarios drop foreign key FK_Usuarios_WEBS; ERROR 1025 (HY000): Error en el renombrado de '.\fz_dev_new\usuarios' a '.\fz_dev_new\#sql2-f1c-21' (Error: 152) 2) mysql> alter table `new2_dbo`.`usuarios` drop foreign key FK_Usuarios_WEBS; Query OK, 195091 rows affected (1 min 12.55 sec) hope this helps!
[21 Feb 2013 7:59]
Muhammad Khan
I have the same problem, and it solved by using like this to drop the foreign key constraint from Innodb table. ALTER TABLE slide_image_sub DROP KEY FK_slide_image_sub instead of using FOREIGN KEY use only KEY, i hope it will help. Thanks
[4 Sep 2015 17:18]
Are you mortal Then prepare to die.
I'm seeing this on 5.5.36 ... CONSTRAINT `genomic_align_ibfk_2` FOREIGN KEY (`method_link_species_set_id`) REFERENCES `method_link_species_set` (`method_link_species_set_id`), ... ALTER TABLE genomic_align DROP FOREIGN KEY genomic_align_block_ibfk_2;ERROR 1025 (HY000): Error on rename of './tapanari_os_bole_lastz_default_81/genomic_align' to './tapanari_os_bole_lastz_default_81/#sql2-7863-74f84f' (errno: 152)
[14 Nov 2017 13:32]
Federico Razzoli
Still in 8.0.3.
Description: When I try ALTER TABLE ORDERS DROP FOREIGN KEY CUSTOMERID; I got: ERROR 1025 (HY000): Error on rename of './TESTDB/ORDERS' to './TESTDB/#sql2-3b5b-8' (errno: 152) But I only want to remove FOREIGN KEY and I don't delete any rows. How to repeat: DROP TABLE IF EXISTS `ORDERS`; CREATE TABLE `ORDERS` ( `ORDERID` int(11) NOT NULL auto_increment, `ORDERDATE` date NOT NULL, `CUSTOMERID` int(11) default NULL, `NETAMOUNT` decimal(12,2) NOT NULL, `TAX` decimal(12,2) NOT NULL, `TOTALAMOUNT` decimal(12,2) NOT NULL, PRIMARY KEY (`ORDERID`), KEY `IX_ORDER_CUSTID` (`CUSTOMERID`), CONSTRAINT `FK_CUSTOMERID` FOREIGN KEY (`CUSTOMERID`) REFERENCES `CUSTOMERS` (`CUSTOMERID`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; DROP TABLE IF EXISTS `CUSTOMERS`; CREATE TABLE `CUSTOMERS` ( `CUSTOMERID` int(11) NOT NULL auto_increment, `FIRSTNAME` varchar(50) NOT NULL, `LASTNAME` varchar(50) NOT NULL, `ADDRESS1` varchar(50) NOT NULL, `ADDRESS2` varchar(50) default NULL, `CITY` varchar(50) NOT NULL, `STATE` varchar(50) default NULL, `ZIP` int(11) default NULL, `COUNTRY` varchar(50) NOT NULL, `REGION` tinyint(4) NOT NULL, `EMAIL` varchar(50) default NULL, `PHONE` varchar(50) default NULL, `CREDITCARDTYPE` int(11) NOT NULL, `CREDITCARD` varchar(50) NOT NULL, `CREDITCARDEXPIRATION` varchar(50) NOT NULL, `USERNAME` varchar(50) NOT NULL, `PASSWORD` varchar(50) NOT NULL, `AGE` tinyint(4) default NULL, `INCOME` int(11) default NULL, `GENDER` varchar(1) default NULL, PRIMARY KEY (`CUSTOMERID`), UNIQUE KEY `IX_CUST_USERNAME` (`USERNAME`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `CUSTOMERS` VALUES (1,'VKUUXF','ITHOMQJNYX','4608499546 Dell Way','','QSDPAGD','SD',24101,'US',1,'ITHOMQJNYX@dell.com',' 4608499546',1,'1979279217775911','2012/03','user1','password',55,100000,'M'),(2,'HQNMZH','UNUKXHJVXB','5119315633 Dell Way','','YNCE RXJ','AZ',11802,'US',1,'UNUKXHJVXB@dell.com','5119315633',1,'3144519586581737','2012/11','user2','password',80,40000,'M'); INSERT INTO `ORDERS` VALUES (10677,'2004-11-10',2,'5.08','0.42','5.50'); ALTER TABLE ORDERS DROP FOREIGN KEY CUSTOMERID;