MySQL – Fixing Table Creation Error: Invalid Position for Identifier

MySQLmysql-workbench

I keeping getting this error during table creation: Order is not valid at this position expecting an identifier. I look up on the Web but I can't figure out how to fix this. What that error means, and how to fix it?

Create Table Customer 
(
CustomerID Int Primary Key,
CustomerName varchar(20) Not Null,
CustomerContactNo varchar (10)Not Null,
CustomerAddress varchar(50)Not Null,
CustomerEmail varchar(25)Not Null
)
ENGINE=INNODB;


Create Table Employee
(
EmployeeID TinyInt Primary Key,
EmployeeName varchar(20) Not Null,
EmployeeContactNo varchar(10)Not Null,
EmployeeEmail varchar(25)Not Null
)
ENGINE=INNODB;


Create Table Order
(
OrderNo Int Primary Key,
Foreign Key(CustomerID) REFERENCES Customer(CustomerID),
Foreign Key(EmployeeID) REFERENCES Employee(EmployeeID)
)ENGINE=INNODB;

Best Answer

You are using a reserved word "Order" as a table name.

This is possible, though still not a good idea, if you (always) embrace the name in back-ticks, i.e.: `Order`. It would probably be better to come up with a different table name, though.

By the way, your Order table has a few other issues as well. You need to add the CustomerID and EmployeeID columns before you can create foreign keys with them:

Create Table `Order` ( 
  OrderNo Int Primary Key, 
  CustomerID Int, 
  EmployeeID TinyInt, 
  Foreign Key(CustomerID) REFERENCES Customer(CustomerID), 
  Foreign Key(EmployeeID) REFERENCES Employee(EmployeeID)
) ENGINE=INNODB;

Also, are you sure tinyint is the data type you want for EmployeeID? It's got a very limited range, only [-128, 127].

Another good idea is to specify your ints (in all the tables) as unsigned, i.e.: CustomerID Int unsigned, since presumably you won't be using negative IDs.

Related Question