mysql requires field names of referenced keys to be specified explicitly

[Neil Zanella]

Hello,

Consider the following PostgreSQL or Oracle SQL DDL code:

CREATE TABLE fooTable (
foo INTEGER,
PRIMARY KEY (foo)
);

CREATE TABLE barTable (
bar INTEGER,
foo INTEGER,
PRIMARY KEY (bar),
FOREIGN KEY (foo) REFERENCES fooTable
);

When the same code is run against MySQL 4.1 the following error is output:

ERROR 1005 (HY000): Can't create table '.\foobar\bartable.frm' (errno: 150)

The reason is that MySQL 4.1 seems to require the names of referenced fields
in the referenced table to be specified even when their names are the same as
ones in the referencing table. Here is how the problem is fixed on MySQL 4.1:

CREATE TABLE barTable (
bar INTEGER,
foo INTEGER,
PRIMARY KEY (bar),
FOREIGN KEY (foo) REFERENCES fooTable (foo)
);

I wonder whether there are plans for future versions of MySQL to relax this
restriction in an attempt to better comply with other RDBMSs' syntax.

Thanks,

Neil

[Bill Karwin]

Neil Zanella wrote:

The reason is that MySQL 4.1 seems to require the names of referenced fields
in the referenced table to be specified even when their names are the same as
ones in the referencing table. Here is how the problem is fixed on MySQL 4.1:

CREATE TABLE barTable (
bar INTEGER,
foo INTEGER,
PRIMARY KEY (bar),
FOREIGN KEY (foo) REFERENCES fooTable (foo)
);
For what it's worth, the following works for me (in MySQL 5.0.2):

CREATE TABLE barTable (
bar INTEGER,
foo INTEGER REFERENCES fooTable,
PRIMARY KEY (bar)
);

But the constraint syntax you were trying still fails in 5.0.2, as you
found it does in MySQL 4.1.
I wonder whether there are plans for future versions of MySQL to relax this
restriction in an attempt to better comply with other RDBMSs' syntax.

I don't think anyone on this newsgroup can answer that.

Regards,
Bill K.