Could you help me why is my CREATE TABLE statement not working?

[layman]

Hello,

 

Could you help me why is my CREATE TABLE statement not working?

CREATE TABLE ORDER (
Order_no		int(6) not null auto_increment,
Order_date		date default '0000-00-00',
key Order_no (Order_no),
PRIMARY KEY (Order_no),
FOREIGN KEY (Customer_id) references CUSTOMER(Customer_id) on update cascade on delete cascade
) ENGINE=INNODB AUTO_INCREMENT=1;

 

The error I am getting is:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER ( Order_no int(6) not null auto_increment, Order_date date default '00' at line 1

 

Server version: 5.1.41

I have created already the Customer table I am referencing, so that one can not be the problem.

Also I took out the default date, but got the same error.

 

Thanks for any help!

 

[Maq]

'ORDER' is a reserved word in MySQL.  You should choose another database name.

http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html

[layman]

Thank you. Put an "s" behind, so now the table is Orders. But still getting the same error!

 

#1072 - Key column 'Customer_id' doesn't exist in table

 

Could you help me why? Thanks for any help.

 

CREATE TABLE ORDERS (
Orders_no		int(6) not null auto_increment,
Orders_date		date default '0000-00-00',
key Orders_no (Orders_no),
PRIMARY KEY (Orders_no),
FOREIGN KEY (Customer_id) references CUSTOMER(Customer_id) on update cascade on delete cascade
) ENGINE=INNODB AUTO_INCREMENT=1;

[Maq]

Not entirely sure, but I think this should be:

FOREIGN KEY (Order_no)

because you want to relate Order_no to the Customer_id from the CUSTOMER table.

[layman]

Thank you! It was accepted!

So if I want to relate Order_no to the Customer_id from the Customer table, it should be always like this?

I mean at the Foreign key part? I thought it has to be the customer id...

 

CREATE TABLE ORDERS (
Orders_no		int(6) not null auto_increment,
Orders_date		date default '0000-00-00',
key Orders_no (Orders_no),
PRIMARY KEY (Orders_no),

FOREIGN KEY (Orders_no) references CUSTOMER(Customer_id) on update cascade on delete cascade

) ENGINE=INNODB AUTO_INCREMENT=1;

 

[Maq]

From what I understand you might want to create a customer_id column in the ORDERS table so you can relate it to the CUSTOMER table for more specific information.  Many to 1 relationship.

[layman]

Yes, that is what I would like to do!

Is this the right way to represent one to many relationships?