"You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax

Question

I tried to create to the table into my database but every time I try it says Connected Successfully. Couldn't create a table.

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near Order( Order_id int(8) AUTO_INCREMENT, Cus_id int(8), Order_date at line 1

Couldn't create table.

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Order(Order_id))' at line 8

I have included the code below.

CREATE TABLE Order(
Order_id int(8) AUTO_INCREMENT,
Cus_id int(8),
Order_date TIMESTAMP,
Primary Key (Order_id),
Foreign Key (Cus_id) References Customer(Cus_id))

CREATE TABLE OrderLine(
Order_id int(8),
P_code int(8),
Quantity float,
Price float,
Constraint PK_Orderline Primary Key (P_code,Order_id),
Foreign Key (P_code) References Product(P_code),
Foreign Key (Order_id) References Order(Order_id))

Solution

ORDER is a Reserved Keyword so if you want to use that name you need to specify it with escaping with backticks:

CREATE TABLE `Order` (...);

It's generally best to avoid table or column names that conflict with MySQL internals, so if this could be renamed, consider doing that.

Comments

[tadman]

Why INT(8) instead of just INT? The 8 is junk that's ignored anyway. Also use DECIMAL(x,y) for prices, don't use FLOAT for anything financial. It will round and mangle your data in all kinds of unfortunate ways.

[PHP Ninja]

add space between Order and ( syntax. MYSQL assume it table name as Order(

[Saadman]

Thanks everyone i just renamed the "Order" to "Orders" and it solved the problem

[Dharman]

Does this answer your question? Syntax error due to using a reserved word as a table or column name in MySQL

[TimBrownlaw]

A suggestion would be to rename your order table to orders ( plural of order ) as it contains multiple orders. Usually (not always) table names are plural. But that is up to you.

[user3153340]

I'm used to write table names in singular, because when writing queries, most of the time you are looking for one specific row, and it is easier and cleaner for me to use SELECT Name, Lastname, Age FROM User [...] rather than SELECT Name, Lastname, Age FROM Users [...]

[tadman]

@user3153340 It's a matter of preference, but in practice the plural form often works out better because it avoids conflict with keywords more often. Thinking of "users" as the set of all users vs. "user" as some particular user helps frame it.