Syntax error when creating table in mysql

Question

I'm using MAMP phpmyadmin and am learning simple sql to create a database where a member can check out a book. I am able to make the two first tables but am failing at the Order table.

Where am I going wrong?

CREATE TABLE if not exists book (
    b_id int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title varchar(30),
    author varchar(50),
    publisher varchar(25)
)

CREATE TABLE if not exists Member (
    m_id int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    firstname varchar(30),
    lastname varchar(30),
    address varchar(20)
)

CREATE TABLE if not exists Order (
    o_id int AUTO_INCREMENT PRIMARY KEY,
    b_id int FOREIGN KEY REFERENCES Book(b_id),
    m_id int FOREIGN KEY REFERENCES Member(m_id)
)

Answer 1

Order is a reserved word in MySQL. You'll need to use it with backticks around it if you want to use it as the name of a table/column.

CREATE TABLE if not exists `Order` ( ...

The column types must also match for the FOREIGN KEY constraints:

b_id int UNSIGNED,
m_id int UNSIGNED

See a demo

Answer 2

In addition to ORDER being a reserved word, and requirement that the data types match exactly, MySQL does not support the syntax of declaring a foreign key inline with the column. You have to declare foreign key constraints separately from columns. Both syntax forms are standard ANSI SQL, but InnoDB supports only table-level constraint declaration.

Here's the statement I tested and got to work on MySQL 5.5.30:

CREATE TABLE if not exists `Order` (
     o_id int AUTO_INCREMENT PRIMARY KEY,
     b_id int unsigned,
     m_id int unsigned,
     FOREIGN KEY (b_id) REFERENCES book(b_id),
     FOREIGN KEY (m_id) REFERENCES Member(m_id)
);