Bug #4711 references constraints in column definitions are ignored
Submitted: 22 Jul 2004 23:59 Modified: 23 Jul 2004 1:55
Reporter: Stefan Proels Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:4.1.3 OS:Linux (Linux)
Assigned to: Matthew Lord CPU Architecture:Any

[22 Jul 2004 23:59] Stefan Proels
Foreign keys defined by a references constraint directly in a column defintion are not created. Only separate "foreign key" constraints are honoured.

How to repeat:
create table test1 (x integer not null primary key) type=InnoDB;

create table test2 (a integer, foreign key (a) references test1(x)) type=InnoDB;
show create table test2;
=> everything is fine.

create table test3 (a integer references test1(x)) type=InnoDB;
show create table test3;
=> the reference is missing.

Trying to violate the reference shows that the foreign key actually was not created in the second version.

Suggested fix:
Create foreign keys in every supported syntax.
[23 Jul 2004 1:55] Matthew Lord
This is not a bug but a current limitation.

From http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html

"Note that the FOREIGN KEY syntax in InnoDB is more restrictive than the syntax presented for 
the CREATE TABLE statement at the beginning of this section: The columns of the referenced 
table must always be explicitly named. "

I cannot change this to a feature request.  If you would like please resubmit this and mark it as a 
feature request.

Best Regards
[23 Jul 2004 4:53] Stefan Proels
But I did name the columns of the referenced table explicitly!

a integer references test1(x)

I understand that merely "a integer references test1" won't work (though that would indeed be a worth a feature request), but the statement above contains all necessary information and is syntactically correct according to the docs.