MySQL Bugs: #47417: Foriegn Key constraints are not respected on self referencing tables

Bug #47417	Foriegn Key constraints are not respected on self referencing tables
Submitted:	17 Sep 2009 14:39	Modified:	17 Sep 2009 17:10
Reporter:	Mysql User	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:	All	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	cascade, foreign key, restrict, self referencing

Description:
A (InnoDB) table created with a self-referencing foreign key without an explicit default value will allow an insert to proceed that does not specify a value for the foreign key column even if the default value for that column is not a valid foreign key.

How to repeat:
DROP TABLE foo;
CREATE TABLE foo
(
	id INTEGER UNIQUE NOT NULL AUTO_INCREMENT,
	parentid INTEGER NOT NULL REFERENCES foo(id) ON DELETE CASCADE ON UPDATE CASCADE
);

INSERT INTO foo () VALUES ();
SELECT * FROM foo;

+----+----------+
| id | parentid |
+----+----------+
|  1 |        0 | 
+----+----------+

Expected result:  Error on insert statement / insert fails.

Suggested fix:
Fix this (and the many other) bug related to ACID compliance in InnoDB, or offer another engine which is ACID compliant.

Your CREATE TABLE statement had NOT really created a foreign key. Check with SHOW CREATE TABLE if you want. This had NOT happened because you missed this point in the manual:

"When you add a foreign key constraint to a table using ALTER TABLE, remember to create the required indexes first."

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

If you create it properly:

mysql> CREATE TABLE foo ( id INTEGER UNIQUE NOT NULL AUTO_INCREMENT, parentid INTEGER NOT NULL, key(parentid), constraint c1 FOREIGN KEY(parentid) REFERENCES foo(id) ON DELETE CASCADE ) engine=InnoDB;
Query OK, 0 rows affected (0.06 sec)

mysql> show create table foo\G*************************** 1. row ***************************       Table: foo
Create Table: CREATE TABLE `foo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parentid` int(11) NOT NULL,
  UNIQUE KEY `id` (`id`),
  KEY `parentid` (`parentid`),
  CONSTRAINT `c1` FOREIGN KEY (`parentid`) REFERENCES `foo` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> INSERT INTO foo () VALUES ();ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`foo`, CONSTRAINT `c1` FOREIGN KEY (`parentid`) REFERENCES `foo` (`id`) ON DELETE CASCADE)

then, as you can see above, constraint works as expected.

Thanks for the help, that works.

However, I didn't "miss" that part of the documentation, I ignored it, as I did not use "ALTER TABLE" to add the constraint; I created it in the CREATE TABLE statement, and there is no such caveat about needing to create indexes or constraints manually in that context.

The SQL99 standard defines 'REFERENCES' as a column-level foreign key constraint that must be honored; simply having a proper REFERENCES statement must perform all the required actions in the storage engine to enforce the foreign key relationship.

Without this compliance to the standard, the REFERENCES expression in the CREATE TABLE statement is useless and may as well not exist; it appears that, alone, it does nothing at all anyway.

Perhaps this should be moved to a documentation and/or standards related bug rather than simply closed.

I agree with your comments about REFERENCES. We had similar requests in the past:

http://bugs.mysql.com/bug.php?id=4919
http://bugs.mysql.com/bug.php?id=17943
http://bugs.mysql.com/bug.php?id=25031

and, maybe, many more. If you prefer "Duplicate" status for this report, I don't mind. The problem is known and, I hope, it will be fixed some day.

Duplicate sounds fine, I did search first but nothing came up, I suppose I should try to massage the search terms a bit more next time.

Should there be a different bug submitted to get the documentation updated to reflect that this issue does not apply only to ALTER TABLE, and that using REFERENCES is basically pointless for the time being, or can this bug be moved to documentation perhaps?

Actually, REFERENCES "usage" is even documented recently. See http://dev.mysql.com/doc/refman/5.1/en/create-table.html:

"Furthermore, InnoDB does not recognize or support “inline REFERENCES  specifications” (as defined in the SQL standard) where the references are defined as part of the column specification. InnoDB accepts REFERENCES clauses only when specified as part of a separate FOREIGN KEY  specification. For other storage engines, MySQL Server parses and ignores foreign key specifications."

Sorry for not providing this reference from the very beginning.