regarding foreign key

[santhanalakshmi]

Hi,
I cant able to create an table with a foreign key :

Syntax :

create table order(orderid int not null,cost int,cid int not null,foreign key(cid) references customer(cid))type=innodb;

Here customer is the other table.


Error Msg:

ERROR 1064 (42000) : you have an error in your sql syntax ;check the manual that corresponds to your MYSQL server version for the right syntax......... ..

thanks....

[Atli]

Hi.

Please post the entire error message. You cut it of just before the part where it explain what was causing the error.

Incidentally, you might want to read the entire error messages more thoroughly. This error, for instance, tells you exactly where the error occurred, so that you can review that part of the query and look up the proper syntax.

[dgreenhouse]

Your initial error is with naming a table "order."

order is a reserved word and must be encapsulated in back-ticks if used.
i.e.
create table `order` (....

Also, it's best to create tables with plurals if possible.
i.e
create table orders (...

This of course avoids needing back-ticks.

See: http://dev.mysql.com/doc/refman/5.0/...ved-words.html

[santhanalakshmi]

hi,

Thanks a lot.......now i can able to create a table with the foreign key....as you told,thanks.

can you tell me the difference between :

create table order(........)

and

create table orders(.......)


reserved word means -> key word ah.

[dgreenhouse]

The difference between the table names is: the table 'orders' - means that the table CONTAINS multiple orders. Whereas a table with the name 'order' would by definition mean it has 1 order in it. Subtle and obviously a table with the name 'order' with multiple records would mean it's an 'orders' table, but the standard naming convention for an 'orders' table is... You guessed it - "Orders!" :-)

[santhanalakshmi]

hi,

thanks for ur response...i got the meaning.