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 22:00
Reporter:	Ian Gulliver
Status:	Open
Category:	Server: InnoDB	Severity:	S4 (Feature request)
Version:	5.0.13	OS:	Linux (Debian Sarge)
Assigned to:	Heikki Tuuri	Target Version:
Triage:	D5 (Feature request)

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.