Mysql Error Errno 152
Contents |
here for a quick overview of the site Help Center Detailed answers to any #1025 - error on rename of (errno 150) questions you might have Meta Discuss the workings and policies of mysql error 1025 errno 150 this site About Us Learn more about Stack Overflow the company Business Learn more about hiring
Mysql Drop Foreign Key
developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is
General Error: 1025 Error On Rename Of Laravel
a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up MySQL Drop foreign key Error 152 up vote 9 down vote favorite 1 I am trying to drop a number of foreign keys using: ALTER TABLE `table` DROP FOREIGN KEY `fk_table_users1` laravel drop foreign key , 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 ca
Verified Impact on me: None Category:MySQL Server: Parser Severity:S2 (Serious) Version:5.0.15, 5.0.74, 5.1.30,
Mysql List Foreign Keys
6.0.9 OS:Linux (Linux Suse 10, Windows) Assigned to: Tags: foreign mysql drop foreign key if exists key, innodb Triage: Triaged: D5 (Feature request) View Add Comment Files Developer Edit Submission View cannot drop index needed in a foreign key constraint Progress Log Contributions [26 Oct 2005 18:53] Vadim Tkachenko Description: When I try ALTER TABLE ORDERS DROP FOREIGN KEY CUSTOMERID; I got: ERROR 1025 http://stackoverflow.com/questions/11649092/mysql-drop-foreign-key-error-152 (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, https://bugs.mysql.com/bug.php?id=14347 `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
`mytable` DROP FOREIGN KEY `keyname` > #1025 - Error on rename of '.\database\mytable' to '.\database\#sql2-6ec-11' > (errno: 152) > > ALTER TABLE `mytable` DROP INDEX http://lists.mysql.com/mysql/204199 `keyname` > #1025 - Error on rename of '.\database\#sql-6ec_13' to '.\database\mytable' > (errno: 150 Oh these are fun. Often times this is because adding foreign keys also adds an index key to the table (on top of the reference key). Why I don't know, but I'm assuming it adds it for optimization since it foreign key knows you'll be hitting that column (otherwise you wouldn't have created the reference in the first place). Moving along... what I do first is SHOW CREATE TABLE `table_name`: | table_name | CREATE TABLE `table_name` ( `id` int(20) unsigned NOT NULL auto_increment, `key_column` smallint(5) unsigned default '1', KEY `key_column` (`key_column`), CONSTRAINT `table_name_ibfk_1` FOREIGN KEY drop foreign key (`key_column`) REFERENCES `second_table` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | So, first you remove the key: ALTER TABLE table_name DROP KEY `key_column`; then the foreign key: ALTER TABLE table_name DROP FOREIGN KEY `table_name_ibfk_1`; Then that should do it. Also note that might also fail if you have, say, another table foreign key referencing to `key_column`. Hope this helps, I also wrote this at about 8AM in the morning while slowly taking in caffeine, so be warned :). Thread• Deleting Foreign KeyMungbeans2Jan • Re: Deleting Foreign KeyChrisWhite4Jan • Re: Deleting Foreign KeyMungbeans5Jan • Re: Deleting Foreign KeyHeikkiTuuri4Jan © 1995, 2016, Oracle Corporation and/or its affiliates Legal Policies Your Privacy Rights Terms of Use Contact Us Page generated in 0.008 sec. Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.