Mysql Error 1217 Foreign Key
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 site About Us rails mysql2::error: cannot delete or update a parent row: a foreign key constraint fails Learn more about Stack Overflow the company Business Learn more about hiring developers cannot update a parent row: a foreign key constraint fails or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack mysql delete foreign key constraint 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 Can't drop table: A foreign key constraint
Delete Row With Foreign Key Constraint Mysql
fails up vote 22 down vote favorite 9 In MySQL I want to drop a table. I tried a lot things but I keep getting the error that the table named bericht can't be dropped. This is the error I'm getting: #1217 - Cannot delete or update a parent row: a foreign key constraint fails How do I drop this table? mysql sql drop-table share|improve this question error 1451 mysql edited Mar 21 '13 at 10:06 CloudyMarble 23.8k1566110 asked Jun 19 '12 at 12:26 roy 116114 2 Find out what other table is referencing it via foreign key, and first drop the FK constraint on that table, then drop this table. –Michael Berkowski Jun 19 '12 at 12:27 You need to remove the requirement by other tables on the one you dont want any more –BugFinder Jun 19 '12 at 12:27 1 Do any of these answers help you? stackoverflow.com/questions/1905470/… stackoverflow.com/questions/3334619/… –Douglas Ludlow Jun 19 '12 at 12:35 add a comment| 3 Answers 3 active oldest votes up vote 36 down vote This should do the trick: SET FOREIGN_KEY_CHECKS=0; DROP TABLE bericht; SET FOREIGN_KEY_CHECKS=1; As others point out, this is almost never what you want, even though it's whats asked in the question. A more safe solution is to delete the tables depending on bericht before deleting bericht. See CloudyMarble answer on how to do that. I use bash and the method in my post to drop all tables in a database when I don't want to or can't delete and recreate the database itself. The #1217 error happens when other tables has foreign ke
here for a quick overview of the site Help Center Detailed answers delete table with foreign key constraints sql to any questions you might have Meta Discuss the workings
Cascade Delete Mysql
and policies of this site About Us Learn more about Stack Overflow the company Business
Foreign_key_checks
Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack http://stackoverflow.com/questions/11100911/cant-drop-table-a-foreign-key-constraint-fails 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 Cannot delete or update a parent row: a foreign key constraint fails up vote 63 down vote favorite 27 When doing: DELETE FROM `jobs` WHERE http://stackoverflow.com/questions/1905470/cannot-delete-or-update-a-parent-row-a-foreign-key-constraint-fails `job_id` =1 LIMIT 1 It errors: #1451 - Cannot delete or update a parent row: a foreign key constraint fails (paymesomething.advertisers, CONSTRAINT advertisers_ibfk_1 FOREIGN KEY (advertiser_id) REFERENCES jobs (advertiser_id)) Here are my tables: CREATE TABLE IF NOT EXISTS `advertisers` ( `advertiser_id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `password` char(32) NOT NULL, `email` varchar(128) NOT NULL, `address` varchar(255) NOT NULL, `phone` varchar(255) NOT NULL, `fax` varchar(255) NOT NULL, `session_token` char(30) NOT NULL, PRIMARY KEY (`advertiser_id`), UNIQUE KEY `email` (`email`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ; INSERT INTO `advertisers` (`advertiser_id`, `name`, `password`, `email`, `address`, `phone`, `fax`, `session_token`) VALUES (1, 'TEST COMPANY', '', '', '', '', '', ''); CREATE TABLE IF NOT EXISTS `jobs` ( `job_id` int(11) unsigned NOT NULL AUTO_INCREMENT, `advertiser_id` int(11) unsigned NOT NULL, `name` varchar(255) NOT NULL, `shortdesc` varchar(255) NOT NULL, `longdesc` text NOT NULL, `address` varchar(255) NOT NULL, `time_added` int(11) NOT NULL, `active` tinyint(1) NOT
1217. Cannot delete or update a parent row: a foreign https://www.euperia.com/development/fix-for-mysql-error-code-1217-a-foreign-key-constraint-fails/1303 key constraint fails' you need to toggle the foreign_key_checks variable first. https://bugs.mysql.com/bug.php?id=70531 The reason for this error is because your tables have foreign keys which rely on the table you're attempting to change. Here's the fix for MySQL error Code 1217 - A foreign key constraint fails: SET foreign_key_checks = 0; DROP TABLE `stats`; SET foreign_key_checks foreign key = 1; I hope this helps someone else. « Inspecting jQuery event handlersJob Control Commands: fg, bg and ctrl-z » AM Andrew McCombe Magento Certified Website Developer Staffordshire, UK Follow @euperia Twitter Google+ Github Latest Posts Magento Snippet: Use a Group By clause on a collection model Managing Homebrew Services I'm a Magento Certified foreign key constraint Developer! Fix for PHP Composer "????%" issue Introducing Hit The Ground: Laravel Edition Magento Custom Variables MySQL Foreign Key Tutorial Testing Magento Email with Mailtrap.io How to backup and restore with Magento 2 Product and Breadcrumb Rich Snippets in Magento Magento Snippet: How to set and get cookies in Magento Solved: PHP Imagick ‘unable to open image' PDF Magento Snippet: Admin grid currency type with currency symbol My Current Podcasts TIL: MySQL FLUSH PRIVILEGES isn't needed MySQL fix: Field ‘xxxx' doesn't have a default value 20 years of PHP #20yearsofphp Useful Oh-My-Zsh plugins Install Beanstalkd on Ubuntu 12.04 How to pear install HTTP2 and HTTP_Request2 Categories Development Linux Magento Miscellaneous Music Review Website Performance About Ads: Whilst we all hate adverts, this site needs to be funded somehow. Therefore you will only ever see one advert per page on this site. If you're using an ad-blocker, please consider unblocking this site. Thanks. Twitter Google+ Github Copyright © Andrew McCombe 1972 - 2016
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, DDL, innodb View Add Comment Files Developer Edit Submission View Progress Log Contributions [5 Oct 2013 11:08] Justin Swanhart Description: When attempting to drop a database populated with data, InnoDB fails to 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 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 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, PRI