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

Re: FOREIGN KEY

zeljko.prince@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

Re: FOREIGN KEY

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/en/create-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

Re: FOREIGN KEY

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

Re: FOREIGN KEY

zeljko.prince@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/en/create-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;

Re: FOREIGN KEY

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