伪造的外键约束失败

我得到这个错误消息:

第40行的错误1217(23000) : 不能 删除或更新父行: 外键约束失败

当我试图摔桌子的时候:

DROP TABLE IF EXISTS `area`;

定义如下:

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;

有趣的是，模式中的 我已经把其他桌子都弄掉了具有针对 area的外键。实际上，除了 area表之外，数据库是空的。

如果数据库中没有任何其他对象，它怎么可能有子行？据我所知，InnoDB 不允许在其他模式上使用外键，对吗？

(我甚至可以运行 RENAME TABLE area TO something_else命令:-?)

223344 次浏览
小开

Disable foreign key checking

SET FOREIGN_KEY_CHECKS=0
小开
最佳答案

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.

小开

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

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

小开

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;
小开

hopefully its work

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

小开

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

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

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

小开

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


提交答案