Bug #8878 foreigh keys in innodb
Submitted: 2 Mar 2005 5:55 Modified: 9 Apr 2006 19:51
Reporter: Michael Gaab Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:Ver 14.7 Distrib 4.1.8 OS:pc- linux(i686)
Assigned to: CPU Architecture:Any

[2 Mar 2005 5:55] Michael Gaab 
Description:
Greetings,

When creating an InnoDB table if a foreign key is declared using only the 'references' keyword inline with the field that is being defined, MySQL will correctly parse the DDL query but it will not refer to the foreign key as a foreign key after the DDL query has been executed. The query is executed as if the 'references' keyword were not even there. The field that was intended to be a foreign key is a simple field only. I am using the InnoDB engine. The fact the the query is ignoring the 'references' keyword can be verified by using the SHOW CREATE TABLE or  DESCRIBE.

How to repeat:
CREATE TABLE shirt (
  id smallint(5) unsigned NOT NULL auto_increment primary key,
  style varchar(10) NOT NULL default '',
  color varchar(10) NOT NULL default '',
  fk_owner smallint(5) unsigned NOT NULL REFERENCES person (id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB

Suggested fix:
1.  Throw an error
2.  Give it the same effect as when using the 'foreign key' keyword
[2 Mar 2005 6:56] Heikki Tuuri 
Mike,

this is in the TODO.
http://www.innodb.com/todo.php

Regards,

Heikki
[9 Apr 2006 19:51] Heikki Tuuri 
Duplicate of http://bugs.mysql.com/bug.php?id=13301