CODEWITHSUNDEEP
phpmysqlsqlphpmyadmin
Kira
Kira

Reputation: 569

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(
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?!!

Upvotes: 1

Views: 115

Answers (2)

fuzzy
fuzzy

Reputation: 97

It is complaining because order is a reserved keyword. Wrapping it with backticks as @TokenMacGuy suggests will solve your problem. Here is a list of reserved keywords

Furthermore, as a general rule, you can transform your entities like below 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

Upvotes: 0

SingleNegationElimination
SingleNegationElimination

Reputation: 156158

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) )

Upvotes: 4

Related Questions