471,108 Members | 1,291 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,108 software developers and data experts.

mysql requires field names of referenced keys to be specified explicitly

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
Jul 23 '05 #1
1 1502
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.
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by 'bonehead | last post: by
3 posts views Thread by dave | last post: by
reply views Thread by Lenz Grimmer | last post: by
13 posts views Thread by S.Dickson | last post: by
Atli
6 posts views Thread by Atli | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.