2

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))
Dharman
  • 30,962
  • 25
  • 85
  • 135
Saadman
  • 33
  • 1
  • 1
  • 5
  • 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. – tadman Oct 08 '19 at 02:58
  • add space between `Order` and `(` syntax. `MYSQL` assume it `table name` as `Order(` – PHP Ninja Oct 08 '19 at 03:00
  • 1
    Thanks everyone i just renamed the "Order" to "Orders" and it solved the problem – Saadman Oct 08 '19 at 03:08
  • Does this answer your question? [Syntax error due to using a reserved word as a table or column name in MySQL](https://stackoverflow.com/questions/23446377/syntax-error-due-to-using-a-reserved-word-as-a-table-or-column-name-in-mysql) – Dharman Sep 20 '20 at 20:33

1 Answers1

4

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.

tadman
  • 208,517
  • 23
  • 234
  • 262
  • 2
    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. – TimBrownlaw Oct 08 '19 at 03:03
  • 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 [...]` – user3153340 Oct 15 '19 at 03:52
  • @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. – tadman Oct 15 '19 at 15:34