creating a join table problem!

I have 3 tables

customer, menu, and order.

The order table is suppose to join the customer and menu tables, and contains the primary keys of both. Here's how I tried to create the order table on phpmyadmin.

create table order(开发者_如何学C
customerID int not null,
itemID int not null,
primary key (customerID, itemID),
foreign key(customerID) reference customer(ID),
foreign key(itemID) reference menu(itemID)
) 

This doesn't work. What am I doing wrong?!!

order is a reserved word, try another name, or quote it, like

create table `order`( 
    customerID int not null,
    itemID int not null,
    primary key (customerID, itemID),
    foreign key(customerID) reference customer(ID),
    foreign key(itemID) reference menu(itemID) ) 

It is complaining as order is a reserved keyword. Wrapping it with backticks as @TokenMacGuy tells you to solves your problem. Here is a list of them

Furthermore, as a general rule, you can transform your entities like so to avoid problems, especially with reserved keywords:-

a) The Entity is always modeled (on paper) as singular as it represents a concept/asset/person in the real world or problem domain. eg. ORDER, CHECK, STUDENT, CAR

b) the corresponding DB Table it is transformed into can always be named using plural. The logic is that the table will contain lots of instances of that Entity. Therefore ORDERS, CHECKS, STUDENTS, CARS