SQL syntax error; "check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Order INT NOT NULL'

Question

I'm using MariaDB and MySQL and I'm trying to create a table using the command prompt but I'm getting a syntax error and I am unsure what is wrong. Below is the error:

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     INT NOT NULL,
        FName   VARCHAR(255),
        LName   VARCHAR(255),
        PhoneNum        VARCH...' at line 3

Here is what I have:

CREATE DATABASE projecttestDB;
USE projecttestDB;

DROP TABLE IF EXISTS Staff;

CREATE TABLE Staff (
    Year    INT NOT NULL,
    Order   INT NOT NULL,
    FName   VARCHAR(255),
    LName   VARCHAR(255),
    PhoneNum    VARCHAR(255),
    Salary  INT, 
    CHECK(Salary>=0),
    PRIMARY KEY (Year, Order)
);

Don't use `Order` as a column name, it is a keyword. – Progman Dec 17 '21 at 22:13 — Progman, Dec 17 '21 at 22:13

score 4 · Answer 1 · answered Dec 17 '21 at 22:14

order is a reserved word in SQL. If you absolutely have to use it, you can escape the column name using backticks:

CREATE TABLE Staff (
    Year    INT NOT NULL,
    `Order` INT NOT NULL, -- Here!
    FName   VARCHAR(255),
    LName   VARCHAR(255),
    PhoneNum    VARCHAR(255),
    Salary  INT, 
    CHECK(Salary>=0),
    PRIMARY KEY (Year, `Order`) -- And here
);

But it's probably a better idea to rename it to something that isn't a reserved word, such as order_num:

CREATE TABLE Staff (
    Year    INT NOT NULL,
    Order_Num INT NOT NULL,
    FName   VARCHAR(255),
    LName   VARCHAR(255),
    PhoneNum    VARCHAR(255),
    Salary  INT, 
    CHECK(Salary>=0),
    PRIMARY KEY (Year, Order_Num)
);

score 0 · Answer 2 · answered Dec 17 '21 at 22:14

0

Order is a system key word. If you change it to something else it will work.

answered Dec 17 '21 at 22:14

MarkBeras

459
1
3
13

SQL syntax error; "check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Order INT NOT NULL'

2 Answers2