FOREIGN KEY

[zeljko.prince]

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

[noone]

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

[zeljko.prince]

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

[zeljko.prince]

It seems there is a bug in MySQL: http://bugs.mysql.com/bug.php?id=13301

[noone]

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;

[zeljko.prince]

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