Bug #4711 references constraints in column definitions are ignored

• Submitted: 22 Jul 2004 23:59
• Modified: 23 Jul 2004 1:55
• Reporter: Stefan Proels
• Status: Not a Bug
• 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

Description:
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.