Bug #10063 ANSI standard updating multiple rows -- foreign key constraints

• Submitted: 21 Apr 2005 15:15
• Modified: 22 Jan 2014 14:59
• Reporter: d d
• Status: Verified
• Category: MySQL Server: InnoDB storage engine
• Severity: S1 (Critical)
• Version: 4.1.11
• OS: Windows (Windows XP)
• CPU Architecture: Any

[21 Apr 2005 15:15] d d

Description:
First, version 4.0 doesn't have this problem.  

The problem:

I tried to "DELETE FROM aTable" that has foreign key references to itself, and it fails saying "Foreign key constraint violation".  ANSI standard says that if you delete, insert or update multiple rows, the foreign keys are evaluated after all statements.

How to repeat:
Set up a table with a foreign key constraint to it's primary key, add some rows and try to delete everything from the table.

Suggested fix:
check foreign key constraints after the all delete's have executed, not after each delete.

[22 Apr 2005 5:32] Jan Lindström

This is in TODO. You could use SET FOREIGN_KEY_CHECKS = 0; before the DELETE.

[30 Sep 2008 10:41] Konstantin Osipov

WL#148

[22 Jan 2014 14:59] Ståle Deraas

Abandoned use of "to be fixed later" setting to status "verified"

[13 Nov 2017 0:25] Federico Razzoli

Still happens in 8.0.3.

mysql> CREATE TABLE t (
    ->   id INT PRIMARY KEY,
    ->   parent INT,
    ->   FOREIGN KEY fk_test (parent)
    ->     REFERENCES t (id)
    ->     ON DELETE RESTRICT
    -> ) ENGINE InnoDB;
Query OK, 0 rows affected (0.32 sec)

mysql> INSERT INTO t (id, parent) VALUES (1, NULL);
Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO t (id, parent) VALUES (2, 1);
Query OK, 1 row affected (0.05 sec)

mysql> DELETE FROM t;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t`, CONSTRAINT `fk_test` FOREIGN KEY (`parent`) REFERENCES `t` (`id`))