Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Bogus foreign key constraint fail

Tags:

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

like image 890
Álvaro González Avatar asked Jul 26 '10 12:07

Álvaro González


People also ask

How do I fix foreign key constraint failure?

The error message itself showing there is a foreign key constraint error, which means you are deleting a parent table where the child table contains the Primary table identifier as a foreign key. To avoid this error, you need to delete child table records first and after that the parent table record.

What is foreign key constraint violation?

Foreign key constraint violation occurred, dbname = <database_name> , table name = <table_name> , constraint name = <constraint_name> . 23000. Occurs when an insert or update on a foreign key table is performed without a matching value in the primary key table.

What is foreign key constraint example?

FOREIGN KEY constraints enforce referential integrity, which essentially says that if column value A refers to column value B, then column value B must exist. For example, given an orders table and a customers table, if you create a column orders.


2 Answers

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

like image 76
Karlis Rode Avatar answered Oct 25 '22 21:10

Karlis Rode


Two possibilities:

  1. There is a table within another schema ("database" in mysql terminology) which has a FK reference
  2. 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.

like image 38
MarkR Avatar answered Oct 25 '22 20:10

MarkR