MySQL Bugs: #13977: InnoDB foreign keys with multiple parent rows act incorrectly

Bug #13977	InnoDB foreign keys with multiple parent rows act incorrectly
Submitted:	12 Oct 2005 20:00	Modified:	13 May 2010 16:04
Reporter:	Ian Gulliver	Email Updates:
Status:	Open	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	5.0.13	OS:	Linux (Debian Sarge)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
When an InnoDB foreign key reference has several parent rows, unexpected behaviour occurs: attempts to delete one of many parent rows in a RESTRICT relationship fail, even when the relationship would still be satisfied.  This is documented:

http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html

as a "Deviation from SQL standards", but it should really be fixed.

How to repeat:
CREATE TABLE `a` (
  `id` int(11) default NULL,
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `b` (
  `id` int(11) default NULL,
  KEY `id` (`id`),
  CONSTRAINT `b_ibfk_1` FOREIGN KEY (`id`) REFERENCES `a` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO a VALUES (1),(1);
INSERT INTO b VALUES (1);

DELETE FROM a WHERE id='1' LIMIT 1;

/* ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails */

Suggested fix:
The last command above should succeed.  It should only fail if executed as:

DELETE FROM a WHERE id='1';

I'm currently having the same problem, using CASCADE. I've written an invoicing application that handles invoices and reminders the same way, only differing in their level (invoice = 0, first reminder = 1, second = 2).

In two cases, I accidentally created a reminder that I deleted afterwards. To my very surprise, the invoice items referencing the invoice number were gone because the reminder row was another parent row with the same number. I was angry and looked for my backup.

The behavior I intended is that invoice items are only to be deleted when all reminders and invoices are deleted, i.e. when there isn't another parent row. Especially if the current behavior deviates from SQL, this should be fixed (and would, sometimes, simplify life a lot). Thank you.

For WL#149 (Foreign keys all storage engines) the decision was:
allowing multiple parent rows will be illegal if one starts the
server with --foreign-key-all-engines=1.