1

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;
1

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.

  • In my database design there is only few employees, so that's why I decided to use smallint. Thank you for help and suggestions I really appreciated. – Curious Nov 4 '18 at 10:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.