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  -

User · Answer

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

User · Answer

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

User · Answer

hopefully its work  SET foreign key checks   0  DROP TABLE table name  SET foreign key checks   1

User · Answer

Disable foreign key checking  SET FOREIGN KEY CHECKS 0

User · Answer

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

User · Answer

On Rails  one can do the following using the rails console   connection   ActiveRecord  Base connection connection execute  SET FOREIGN KEY CHECKS 0

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] Bogus foreign key constraint fail

Examples related to mysql

Examples related to innodb