Bogus foreign key constraint fail

mysql innodb

I get this error message:

ERROR 1217 (23000) at line 40: Cannot delete or update a parent row: a foreign key constraint fails

... when I try to drop a table:

DROP TABLE IF EXISTS `area`;

... defined like this:

CREATE TABLE `area` (
  `area_id` char(3) COLLATE utf8_spanish_ci NOT NULL,
  `nombre_area` varchar(30) COLLATE utf8_spanish_ci NOT NULL,
  `descripcion_area` varchar(100) COLLATE utf8_spanish_ci NOT NULL,
  PRIMARY KEY (`area_id`),
  UNIQUE KEY `nombre_area_UNIQUE` (`nombre_area`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci;

The funny thing is that I already dropped all other tables in the schema that have foreign keys against area. Actually, the database is empty except for the area table.

How can it possibly have child rows if there isn't any other object in the database? As far as I know, InnoDB doesn't allow foreign keys on other schemas, does it?

(I can even run a RENAME TABLE area TO something_else command :-?)

Is it possible that the table is a part of a Referential-Integrity relationship in another schema?

I have some other copies of the app so it's always possible. However, the syntax I use is basically CONSTRAINT fk_servicio_area1 FOREIGN KEY (area_id) REFERENCES area (area_id), i.e., no schema name on the table reference :-?

Karlis Rode

On demand, now as an answer...

When using MySQL Query Browser or phpMyAdmin, it appears that a new connection is opened for each query (bugs.mysql.com/bug.php?id=8280), making it neccessary to write all the drop statements in one query, eg.

SET FOREIGN_KEY_CHECKS=0; 
DROP TABLE my_first_table_to_drop; 
DROP TABLE my_second_table_to_drop; 
SET FOREIGN_KEY_CHECKS=1;

Where the SET FOREIGN_KEY_CHECKS=1 serves as an extra security measure...

For those creating a dump using phpMyAdmin, there is an option "Disable foreign key checks" that will automatically add SET FOREIGN_KEY_CHECKS=0; to the beginning of the dump.

Seems like phpMyAdmin has implemented this lovely feature, now I'm waiting for mysqlWorkbench to do the same! :)

@CodeMed FYI, I accepted MarkR's answer because it provides an explanation for the issue that makes sense—though I admit I couldn't verify it since I haven't faced this same issue in the 6 following years, not even once. This and earlier answers provide a workaround (great for that good) but don't really address the question itself and since you can only accept one answer I had to choose.

Warning: this is not a solution but only lazy man's workaround. After using this (with records on some other tables pointing to the dropped table), you will experience dangling foreign keys which fatally breaks the consistency (the C in ACID) of your database and your applications will start to throw exceptions all over the place. You've been warned.

Though I am sure bekce's warning should be understood and heeded, this solution did work for me, in a situation where I was confident that I was also dropping all the tables that were pointing to the tables with the troublesome foreign key constraints.

MarkR

Two possibilities:

There is a table within another schema ("database" in mysql terminology) which has a FK reference The innodb internal data dictionary is out of sync with the mysql one.

You can see which table it was (one of them, anyway) by doing a "SHOW ENGINE INNODB STATUS" after the drop fails.

If it turns out to be the latter case, I'd dump and restore the whole server if you can.

MySQL 5.1 and above will give you the name of the table with the FK in the error message.

I can no longer reproduce the issue. The out of sync dictionary stands out as a likely reason. I'll test it day and see what SHOW ENGINE INNODB STATUS reports.

Thank you for this answer! I had a many-to-many table still referencing the table we couldn't drop, so I had to drop that table first.

SHOW ENGINE INNODB STATUS lists the last foreign key error under "LATEST FOREIGN KEY ERROR". This has a timestamp.

there could be a table still having a reference key to the subject table. it was in my case, like this.

Saved a lot of time. Dropped the db under "LATEST FOREIGN KEY ERROR"

Álvaro González

Disable foreign key checking

SET FOREIGN_KEY_CHECKS=0

The correct command appears to be SET FOREIGN_KEY_CHECKS=0 and it does fix the error message. Do you have any idea about why this is required? Are foreign keys cached even after the tables are gone?

Well to say the truth, I have no idea why such a problem arises, but make sure you disable key checking every time you make some huge changes or updates. It has happened to me several times, leaving me without sleep for days.

Do make sure to SET FOREIGN_KEY_CHECKS=1; after you are done!

SET FOREIGN_KEY_CHECKS=0; DROP TABLE my_first_table_to_drop; DROP TABLE my_second_table_to_drop; SET FOREIGN_KEY_CHECKS=1;

Where the SET FOREIGN_KEY_CHECKS=1 serves as an extra security measure...

@KarlisRode, Bravo for the comment on phpMyAdmin. If you were to put that as an answer, I would +1 it.

JackD

from this blog:

You can temporarily disable foreign key checks:

SET FOREIGN_KEY_CHECKS=0;

Just be sure to restore them once you’re done messing around:

SET FOREIGN_KEY_CHECKS=1;

Nice answer as I was developing on local :)

It's a valid workaround (I can confirm it works) but the linked blog entry doesn't really talk about the scenario in this question (a database that's already empty save for one table).

M_ Fa

hopefully its work

SET foreign_key_checks = 0; DROP TABLE table name; SET foreign_key_checks = 1;

Yes, it works, as the several times it's been mentioned before ;-)

yeyo

On Rails, one can do the following using the rails console:

connection = ActiveRecord::Base.connection
connection.execute("SET FOREIGN_KEY_CHECKS=0;")

Vadim Pluzhinsky

Maybe you received an error when working with this table before. You can rename the table and try to remove it again.

ALTER TABLE `area` RENAME TO `area2`;
DROP TABLE IF EXISTS `area2`;

Abdulrahman K

i found an easy solution, export the database, edit it what you want to edit in a text editor, then import it. Done

That's an interesting solution, that probably shouldn't actually happen. Instead, anything that needs to be changed should be done through the DBMS. Editing a database dump in a text editor seems like a ripe avenue for problems.

I don't really understand what you're into. Dumping the database, removing the CREATE TABLE code and loading the dump again... won't make MySQL remove the table. And if you mean restoring the dump in a new database... If you want to wipe out all tables like me, a newly created database will already be empty. If you want to keep some tables, the SET FOREIGN_KEY_CHECKS=0 workaround mentioned everywhere here works fine and is simpler; and you probably don't need to edit the dump anyway since the new copy of your data will possibly not have an out-of-sync data dictionary.

Aadil Masavir

Cannot delete or update a parent row: a foreign key constraint fails (table1.user_role, CONSTRAINT FK143BF46A8dsfsfds@#5A6BD60 FOREIGN KEY (user_id) REFERENCES user (id))

What i did in two simple steps . first i delete the child row in child table like

mysql> delete from table2 where role_id = 2 && user_id =20;

Query OK, 1 row affected (0.10 sec)

and second step as deleting the parent

delete from table1 where id = 20;

Query OK, 1 row affected (0.12 sec)

By this i solve the Problem which means Delete Child then Delete parent

i Hope You got it. :)

Please read the question again. You cannot remove a table that doesn't exist.

in this scenario we can remove foreign key constraint then try to delete the table. we can Drop foreign key like this ALTER TABLE DROP CONSTRAINT

Bogus foreign key constraint fail

Follow WeChat

Want to stay one step ahead of the latest teleworks?

相似问题

Platform

Support

Links

Contact US