470,647 Members | 1,178 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,647 developers. It's quick & easy.

FOREIGN KEY

Given the following table:
CREATE table1 (a INTEGER PRIMARY KEY) TYPE=INNODB;

It is possible to create a relation between table1 i table2 using the
following syntax:
CREATE table2 (a INTEGER PRIMARY KEY, b INTEGER, c INTEGER, FOREIGN
KEY(b) REFERENCES table1(a)) TYPE=INNODB;

How can I make that relation within the column definition? This won't
work:
CREATE table2 (a INTEGER PRIMARY KEY, b INTEGER REFERENCES table1(a), c
INTEGER) TYPE=INNODB;
Sincerly,
Zeljko

Mar 14 '06 #1
5 1273
ze***********@gmail.com wrote:
Given the following table:
CREATE table1 (a INTEGER PRIMARY KEY) TYPE=INNODB; It is possible to create a relation between table1 i table2 using the
following syntax:
CREATE table2 (a INTEGER PRIMARY KEY, b INTEGER, c INTEGER, FOREIGN
KEY(b) REFERENCES table1(a)) TYPE=INNODB; How can I make that relation within the column definition? This won't
work:
CREATE table2 (a INTEGER PRIMARY KEY, b INTEGER REFERENCES table1(a), c
INTEGER) TYPE=INNODB;

http://dev.mysql.com/doc/refman/4.1/en/alter-table.html

CREATE table2 (a INTEGER PRIMARY KEY, b INTEGER, c INTEGER) TYPE=INNODB;
alter table table2 add foreign key fk_table1_key (b) references table1(a);

Sincerly,
Zeljko

Mar 14 '06 #2

noone wrote:
http://dev.mysql.com/doc/refman/4.1/en/alter-table.html

CREATE table2 (a INTEGER PRIMARY KEY, b INTEGER, c INTEGER) TYPE=INNODB;
alter table table2 add foreign key fk_table1_key (b) references table1(a);


Thank you for fast reply.
I am currently using the ALTER TABLE statement, but I wanted somehow to
eliminate it and make reference within table definition.

According to http://dev.mysql.com/doc/refman/5.0/...ate-table.html I
shoud acomplish that with the following query:

CREATE TABLE table2 (a INTEGER PRIMARY KEY, b INTEGER REFERENCES
table1(a), c INTEGER) TYPE=INNODB;

The previous query executes, but no relations are created.

(CREATE TABLE tbl_name (col_name type reference_definition))
Zeljko

Mar 14 '06 #3
It seems there is a bug in MySQL: http://bugs.mysql.com/bug.php?id=13301

Mar 14 '06 #4
ze***********@gmail.com wrote:

noone wrote:
http://dev.mysql.com/doc/refman/4.1/en/alter-table.html

CREATE table2 (a INTEGER PRIMARY KEY, b INTEGER, c INTEGER) TYPE=INNODB;
alter table table2 add foreign key fk_table1_key (b) references table1(a);
Thank you for fast reply.
I am currently using the ALTER TABLE statement, but I wanted somehow to
eliminate it and make reference within table definition. According to http://dev.mysql.com/doc/refman/5.0/...ate-table.html I
shoud acomplish that with the following query:


According the docs the syntax is (not tested):

CREATE TABLE table2 (a INTEGER PRIMARY KEY, b INTEGER constraint
[constr-name] foreign key REFERENCES table1(a), c INTEGER) TYPE=INNODB;

Mar 14 '06 #5
noone wrote:
According the docs the syntax is (not tested):

CREATE TABLE table2 (a INTEGER PRIMARY KEY, b INTEGER constraint
[constr-name] foreign key REFERENCES table1(a), c INTEGER) TYPE=INNODB;


It breaks on both of the following queries (with or without CONSTRAINT
keyword):

CREATE TABLE table2 (a INTEGER PRIMARY KEY, b INTEGER constraint
whatever foreign key REFERENCES table1(a), c INTEGER) TYPE=INNODB;

CREATE TABLE table2 (a INTEGER PRIMARY KEY, b INTEGER foreign key
REFERENCES table1(a), c INTEGER) TYPE=INNODB;
Never mind, I'll put FOREIGN KEY contstraints in table definition,
although I wanted them in the column definition. As I understand, there
is a bug in MySQL, as stated in my previous post. Thanks anyway.
Zeljko

Mar 15 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Jeremiah Jacks | last post: by
2 posts views Thread by geoff | last post: by
10 posts views Thread by Bodza Bodza | last post: by
4 posts views Thread by Simon Bond | last post: by
10 posts views Thread by Shawn Chisholm | last post: by
2 posts views Thread by Ian Davies | last post: by
1 post views Thread by Korara | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.