MYSQL Error 1064: Can't seem to find the error exactly

Question

Hello I'm new to SQL and coding in general, and I'm seeing an error in my code and I can't seem to figure it out.

DROP TABLE IF EXISTS record, artist;

CREATE TABLE artist (
    id INT AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    PRIMARY KEY (id)
);

CREATE TABLE record (
    id INT AUTO_INCREMENT,
    title VARCHAR(50),
    artist_id INT,
    genre TINYTEXT,
    year YEAR(4),
    price DECIMAL(10, 2) unsigned,
    PRIMARY KEY (id),
    FOREIGN KEY (artist_id)
            REFERENCES artist (id)
);

CREATE TABLE order (
    id INT AUTO_INCREMENT,
    record_id INT,
    quantity INT unsigned,
    total DECIMAL(10, 2) unsigned,
    PRIMARY KEY (id),
    FOREIGN KEY (record_id)
            REFERENCES record (id)
);

The error that shows up is:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order ( id INT AUTO_INCREMENT, record_id INT, quantity INT unsigned, total D' at line 1

I know it's telling me exactly where the error is, and I know it's a syntax error, but for some reason I just can't seem to understand where or what the error is exactly. I've tried adding 'order' with the DROP TABLE IF EXISTS but that just comes up with more errors. I know it's probably something really obvious so I'm sorry for asking but I'm so lost.

Answer 1

order is a reserved word in SQL. You could either protect it by surrounding it with forward quotes:

CREATE TABLE `order` (
    id INT AUTO_INCREMENT,
    record_id INT,
    quantity INT unsigned,
    total DECIMAL(10, 2) unsigned,
    PRIMARY KEY (id),
    FOREIGN KEY (record_id)
            REFERENCES record (id)
);

or better yet, just find a name that ins't a reserved word, e.g., orders:

CREATE TABLE orders (
    id INT AUTO_INCREMENT,
    record_id INT,
    quantity INT unsigned,
    total DECIMAL(10, 2) unsigned,
    PRIMARY KEY (id),
    FOREIGN KEY (record_id)
            REFERENCES record (id)
);