MySQL Bugs: #7427: FOREIGN KEY colname REFERENCES ignored in InnoDB

Bug #7427	FOREIGN KEY colname REFERENCES ignored in InnoDB
Submitted:	20 Dec 2004 11:57	Modified:	6 Sep 2005 14:02
Reporter:	Anders Karlsson	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	4.1.7	OS:	Windows (Windows)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Using the syntax:
CREATE TABLE tabname(colname ... REFERENCES)
to define a FOREIGN KLEY is silently ignored in InnoDB

How to repeat:
Create a master table:
CREATE TABLE t1(c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;

Create a referencing table:
CREATE TABLE t2(c1 INT NOT NULL REFERENCES t1(c1));
SHOW CREATE TABLE t2;
The foreign key is not there.

Possible workaround is:
CREATE TABLE t2(c1 INT NOT NULL, FOREIGN KEY(c1) REFERENCES t1(c1));
The foreign key is there.

Anders,

I am changing the status to a feature request. About 10 people have requested this feature. Maybe time to implement it next spring.

You can follow the progress from:
http://www.innodb.com/todo.php

Regards,

Heikki

I think that the severity of this bug should be changed to something more critical. The DBMS should at least throw an error, and not just silently ignore the constraint (short term solution); and then - when the developers get time to implement the syntax - accept+respect it, actually creating the constraint.

Hi Heikki,

This has been coming up again. Any thoughts on when it can be done?

  -Brian

Duplicate of bug #4919.