Bug #46301 Foreign keys: delete w/o where does not respect RESTRICT FKs for InnoDB tables
Submitted: 20 Jul 2009 9:43 Modified: 20 Dec 2013 6:51
Reporter: Dmitry Lenev Email Updates:
Status: Won't fix Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:6.1-fk OS:Any
Assigned to: CPU Architecture:Any

[20 Jul 2009 9:43] Dmitry Lenev
I'm running server built from latest snapshot from mysql-6.1-fk tree in --foreign-key-all-engines=1 mode. I've created InnoDB table with self-referencing foreign key with RESTRICT as ON DELETE action and populated it with several rows referencing to each other. I'm trying to empty this table using DELETE without WHERE clause and don't get error which is expected according to the standard/specification.

Same scenario is handled appropriately if I use Falcon.

See How-to-repeat for details.

How to repeat:
--echo # Script for mysqltest which reproduces the problem...
--source include/have_innodb.inc

set storage_engine= InnoDB;
let $engine_type= InnoDB;

create table t1 (pk int primary key,
                 fk int references t1 (pk) on delete restrict);
insert into t1 values (1, 5), (5, 5), (3, 5);
--echo # Error is expected but not emitted...
delete from t1;
drop table t1;
[20 Jul 2009 12:42] MySQL Verification Team
Thank you for the bug report. Verified as described.
[20 Dec 2013 6:51] Erlend Dahl
6.x project was abandoned years ago.