Bogus foreign key constraint fail

Question

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? — Raj More, Jul 26 '10 at 12:21
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 :-? — Álvaro González, Jul 26 '10 at 12:38

score 121 · Answer 1 · answered Nov 07 '13 at 20:06

121

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...

answered Nov 07 '13 at 20:06

Karlis Rode

3,643
2
16
16

2

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. – Mike Aug 13 '14 at 18:04
Seems like phpMyAdmin has implemented this lovely feature, now I'm waiting for mysqlWorkbench to do the same! :) – Karlis Rode Apr 14 '16 at 13:58
@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. – Álvaro González Jun 01 '16 at 14:27
1

**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](https://en.wikipedia.org/wiki/ACID)) of your database and your applications will start to throw exceptions all over the place. You've been warned. – bekce Apr 07 '17 at 11:17
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. – user1147171 Apr 18 '17 at 02:06

score 105 · Accepted Answer · answered Jul 26 '10 at 12:58

105

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.

answered Jul 26 '10 at 12:58

MarkR

62,604
14
116
151

1

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. – Álvaro González Jul 27 '10 at 06:29
3

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. – Christian Oudard Aug 24 '11 at 13:48
5

SHOW ENGINE INNODB STATUS lists the last foreign key error under "LATEST FOREIGN KEY ERROR". This has a timestamp. – bbrame Feb 04 '14 at 20:42
there could be a table still having a reference key to the subject table. it was in my case, like this. – R T Nov 12 '14 at 12:28
1

Saved a lot of time. Dropped the db under "LATEST FOREIGN KEY ERROR" – san1512 Sep 16 '16 at 14:07

score 48 · Answer 3 · edited Jun 01 '16 at 14:27

48

Disable foreign key checking

SET FOREIGN_KEY_CHECKS=0

edited Jun 01 '16 at 14:27

Álvaro González

142,137
41
261
360

answered Jul 26 '10 at 12:09

Flakron Bytyqi

3,234
20
20

62

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? – Álvaro González Jul 26 '10 at 12:16
1

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. – Flakron Bytyqi Jul 26 '10 at 12:19
55

Do make sure to `SET FOREIGN_KEY_CHECKS=1;` after you are done! – pedro_sland Nov 13 '10 at 18:23
5

When using MySQL Query Browser or phpMyAdmin, it appears that a new connection is opened for each query (http://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... – Karlis Rode Jul 02 '13 at 19:01
1

@KarlisRode, Bravo for the comment on phpMyAdmin. If you were to put that as an answer, I would +1 it. – Sablefoste Sep 24 '13 at 18:27
1

@ÁlvaroG.Vicario please make an answer from your comment, as it seems to be supported by more people than the answer itself. – Ian Clark Jun 02 '14 at 08:40

score 28 · Answer 4 · answered Apr 19 '14 at 07:49

28

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;

answered Apr 19 '14 at 07:49

JackD

289
3
2

Nice answer as I was developing on local :) – Adelin Aug 22 '15 at 10:56
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). – Álvaro González Jun 01 '16 at 14:30

score 6 · Answer 5 · answered Feb 26 '15 at 17:31

6

hopefully its work

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

answered Feb 26 '15 at 17:31

M_ Fa

487
10
11

Yes, it works, as the several times it's been mentioned before ;-) – Álvaro González Jun 01 '16 at 14:31

score 1 · Answer 6 · answered Sep 07 '15 at 19:20

1

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

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

answered Sep 07 '15 at 19:20

yeyo

2,954
2
29
40

score 0 · Answer 7 · answered Sep 08 '13 at 15:46

0

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`;

answered Sep 08 '13 at 15:46

Vadim Pluzhinsky

51
1
4

score 0 · Answer 8 · answered Jun 08 '16 at 21:43

0

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

answered Jun 08 '16 at 21:43

Abdulrahman K

1

4

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. – Brandon Anzaldi Jun 08 '16 at 22:00
1

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. – Álvaro González Jun 09 '16 at 07:44

score -1 · Answer 9 · answered Jan 18 '16 at 14:17

-1

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. :)

answered Jan 18 '16 at 14:17

Aadil Masavir

69
4

Please read the question again. You cannot remove a table that doesn't exist. – Álvaro González Jan 18 '16 at 15:11
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 – Aadil Masavir Jan 20 '16 at 05:12

Bogus foreign key constraint fail

9 Answers9

Linked

Related