Error in CREATE table statement in MySQL

Question

I wanted to create a table ORDER in database called Projects and which has another table CUSTOMER.

Here are the statements I used :

mysql> Create Table ORDER(
    -> OrderNo Integer(5) Primary Key,
    -> CustNo Integer(7),
    -> ItemName Varchar(30),
    -> Qty Integer(5),
    -> Price Decimal(6,2) Not Null,
    -> Foreign Key (CustNo) references CUSTOMER(CustID));

but I am getting this error :

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 to use near 'ORDER(
OrderNo Integer(5) Primary Key,
CustNo Integer(7),
ItemName Varchar(30),
' at line 1

What is wrong here?

Answer 1

As the comments mentioned, ORDER among others are reserved in MySQL. The complete list is in the documentation here.

You have three options, either to change the table name (my personal recommendation), escape the name, or qualify the name with the database such as: CREATE TABLE mydb.ORDER ..... If you do choose to stick with a keyword named table (or column), you run the risk of confusion later if you forget to escape it in another query. If this is tied to customers, perhaps it could be the 'CustomerOrder' table.

CREATE TABLE `ORDER`(
    OrderNo Integer(5) Primary Key,
    CustNo Integer(7),
    ItemName Varchar(30),
    Qty Integer(5),
    Price Decimal(6,2) Not Null,
    Foreign Key (CustNo) references CUSTOMER(CustID)
);

Given that you have a primary key, you may also want it to auto increment so you don't have to create the keys manually.

CREATE TABLE `ORDER`(
    OrderNo Integer(5) Primary Key AUTO_INCREMENT,
    CustNo Integer(7),
    ItemName Varchar(30),
    Qty Integer(5),
    Price Decimal(6,2) Not Null,
    Foreign Key (CustNo) references CUSTOMER(CustID)
);