Mysql Drop Table Error 1217
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this cannot delete or update a parent row a foreign key constraint fails mysql site About Us Learn more about Stack Overflow the company Business Learn more rails mysql2::error: cannot delete or update a parent row: a foreign key constraint fails about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x mysql delete foreign key constraint Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up MySQL drop cannot update a parent row: a foreign key constraint fails table up vote 1 down vote favorite 1 I want to drop a table with drop table EMPLOYEE; but I get the error: #1217 - Cannot delete or update a parent row: a foreign key constraint fails I looked around on the internet to show the hidden constraints and found: CREATE TABLE `EMPLOYEE` ( `Ssn` int(9) NOT NULL, `Dno` int(11) NOT NULL, UNIQUE KEY`Ssn_8`
Error 1451 Mysql
(`Ssn`), UNIQUE KEY`Dno_13` (`Dno`), CONSTRAINT `EMPLOYEE_ibfk_1` FOREIGN KEY(`Dno`) REFERENCES `DEPARTMENT` (`Dnumber`), CONSTRAINT `EMPLOYEE_ibfk_2` FOREIGN KEY(`Dno`) REFERENCES `DEPARTMENT` (`Dnumber`), CONSTRAINT `EMPLOYEE_ibfk_3` FOREIGN KEY(`Dno`) REFERENCES `EMPLOYEE` (`Dno`), CONSTRAINT `EMPLOYEE_ibfk_4` FOREIGN KEY(`Dno`) REFERENCES `EMPLOYEE` (`Dno`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `DEPARTMENT` ( `Dnumber` int(11) NOT NULL, `Mgr_ssn` int(9) NOT NULL, UNIQUE KEY`Mgr_ssn` (`Mgr_ssn`), UNIQUE KEY`Dnumber` (`Dnumber`), CONSTRAINT `DEPARTMENT_ibfk_1` FOREIGN KEY(`Mgr_ssn`) REFERENCES `EMPLOYEE` (`Ssn`), CONSTRAINT `DEPARTMENT_ibfk_2` FOREIGN KEY(`Mgr_ssn`) REFERENCES `EMPLOYEE` (`Ssn`), CONSTRAINT `DEPARTMENT_ibfk_3` FOREIGN KEY(`Mgr_ssn`) REFERENCES `DEPARTMENT` (`Mgr_ssn`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 After finding this I tried dropping the contraints first with: alter table EMPLOYEE drop contraint 'EMPLOYEE_ibfk_1'; but I keep getting: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''EMPLOYEE_ibfk_1'' at line 1 I have worked at dropping this table for several hours now and searched many topics on the internet. People ended up dropping the db, but I am unauthorized to drop db or create a db. mysql foreign-keys sql-drop share|improve this question edited Mar 24 '14 at 19:31 Kara 3,16973147 asked Apr 22 '12 at 22:04 AL90 8115 Looks lik
2013 6:33 Reporter: Justin Swanhart Email Updates: Status: Verified Impact on me: None Category:MySQL Server: DDL Severity:S1 (Critical) Version:5.5.32,5.5.34, 5.6.14, 5.7.2 OS:Any (5.5.32-ubuntu) Assigned to: Tags: constraints,
Foreign_key_checks
DDL, innodb View Add Comment Files Developer Edit Submission View Progress Log Contributions mysql on delete cascade [5 Oct 2013 11:08] Justin Swanhart Description: When attempting to drop a database populated with data, InnoDB fails to remove foreign key constraint drop one of the tables with an FK error. It is not possible to drop the database without turning off FK checks. An orphaned table with constraints that can not be satisfied http://stackoverflow.com/questions/10272526/mysql-drop-table remains in the schema. I consider this corruption as the table can not be dropped without setting foreign_key_checks=0 and inconsistent data is now in the database (children without parents). How to repeat: I haven't narrowed down an exact case yet, and I can't give you the table structures of all the tables as they are private. Here are reproduce steps though and a sanitized https://bugs.mysql.com/bug.php?id=70531 version of the table that is orphaned. mysql> select count(*) from information_schema.tables where table_schema='XXX'; +----------+ | count(*) | +----------+ | 17 | +----------+ 1 row in set (0.00 sec) mysql> drop database XXX; ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails mysql> select count(*) from information_schema.tables where table_schema='XXX'; +----------+ | count(*) | +----------+ | 1 | +----------+ 1 row in set (0.00 sec) mysql> drop database XXX; ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails mysql> use XXX; Database changed mysql> show tables; +---------------+ | Tables_in_pct | +---------------+ | XXXXXX | +---------------+ 1 row in set (0.00 sec) mysql> show create table XXXXXX\G *************************** 1. row *************************** Table: XXXXXX Create Table: CREATE TABLE `XXXXXX` ( `XXXXXX_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `XXX_XX` int(10) unsigned NOT NULL, `XXXXX_config_id` int(10) unsigned NOT NULL, `XXXX` char(36) NOT NULL, `XXXXXXXh` varchar(50) NOT NULL, `XXXXXXXXXXXX` varchar(50) DEFAULT NULL, `XXX` varchar(50) DEFAULT NULL, `XXXXXX` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `XXXXX_XXXXXX_XX` timestamp NULL DEFAULT NULL, `XXX_XXXXX` timestamp NULL DEFAULT NULL, `XX` timestamp NULL DEFAULT NULL, PRIMARY KEY (`XXXXX`), KEY `xxx_xx` (`XXX_XX`), KEY `xxxx
Barré Email Updates: Status: Closed Impact on me: None Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S2 (Serious) https://bugs.mysql.com/bug.php?id=69008 Version: 7.3.1 m2 OS:Microsoft Windows ( (Server 2008 R2 64-bit)) Assigned to: Triage: Needs Triage: D2 (Serious) View Add Comment Files Developer Edit Submission View Progress Log Contributions [19 Apr 2013 9:48] Sébastien Barré Description: If I create two simple tables linked with foreign keys, I am then unable to drop the database foreign key (error 23000: Cannot delete or update a parent row: a foreign key constraint fails). After the drop instruction, the db is left with a subset of the tables removed. Trying to remove one table gives me "Unknown table", although the table is indeed deleted. Below in "how to repeat" section, I'll give a simple foreign key constraint example that reproduces that and finally allows to drop the database. However with our real-world database, that has more tables and referential constraints, I have not been able to drop the database, the only way around being to remove the database files from the filesystem (that said, I realize that probably removing the foreign key constraints one by one, then dropping the DB would have probably been a workaround). As a final note, I tried "set foreign_key_checks=0", but that has no effect. I guess it is not propagated to the ndb engine. Our cluster setup is exactly the same as described here: http://bugs.mysql.com/bug.php?id=68224 (obviously ported to the 7.3.1m2 release of mysql cluster) How to repeat: mysql>create database test; Query OK, 1 row affected (0.06 sec) mysql>use test; Database changed mysql>create fktest (id int primary key) engine=ndb; Query OK, 0 rows affected (0.23 sec) mysql>create fktest2 (id int, foreign(id) references fktest(id)) engine=ndb; Query OK, 0 rows affected (0.27 sec) mysql>drop d