How to drop tables with cyclic foreign keys in MySQL


Problem :

I have two tables, Parent and Child. The column Parent.favorite_child has a foreign key constraint pointing to Child.id. And the column Child.parent has a foreign key constraint pointing to Parent.id.

Now here's the weird part. I'm trying to drop my Child table, which contains no records, but MySQL is giving me the error:

ERROR 1217: Cannot delete or update a parent row: a foreign key constraint fails
SQL Statement:
drop table `mydatabase`.`Child`

Why is MySQL throwing this error? There are literally no records in the Child table with which anything could be pointing to or from.



Solution :

You need to first drop the foreign key on the parent table before you can delete the child table:

ALTER TABLE `Parent` DROP FOREIGN KEY `Parent_ibfk_1` ;

Mysql Tutorials

Mysql Howto..

How to close unclosed mysql connections?

How can I create Mirror of amazon MySQL RDS instance and connect to it?

how to select a row from ajax table in php

How can i export data in CSV to be used in PHP from mysql

how can google find me if I am inside a mysql table?

How do i see the EER Model for an mySQL database i have created using DDL

How to judiciously apply indexes in mysql

How to insert button for every row so that the data for that particular row can be duplicated into the database

PHP\MYSQL\AJAX(?) - Struggling with how to dynamically update form without invoking page refresh

How to install MySQLi

MySQL Workbench: how to setup “ON UPDATE”? and CURRENT_TIMESTAMP?

How to fetch mysql database data with the help of wp-config file in wordpress

How to UPDATE and SELECT at the same time in MySQL

How do I keep a MySQL Fulltext Index up to date?

If duplicate key, how do I determine which primary key (multi) triggered it

How are regular and composite indexes implemented in RDBs?

How can I get a database out of a Windows 7 MySQL Database stored on a backup hard drive?

How to auto import wordpress contents from custom mysql database?

How to clear Error in populating list view from mysql? [Info Updated]

how to get id of last inserted row [duplicate]

How to build a new MySQL Table from 2 MySQL Tables without PHP

MySQL - how to find most commonly occuring “tag” in a list of rows

How to optimize this low-performance MySQL query?

How to display data between two dates in php using mysql? [closed]

How to increase length of a String in mysql while mapping using JPA

How to identify default_week_format in MySQL?

How to perform 'between' in Arabic (hijri) calendar and save it as 'date' in MySQL?

How do I connect to different databases at run time?

How to select the sum of distinct values in a table using mysql

How to insert timestamp into mysql with php properly?