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)
)

What is the error message you receive?

cdhowie
– cdhowie

2013-04-11 17:42:39 +00:00
Commented Apr 11, 2013 at 17:42 — cdhowie
– cdhowie, Commented Apr 11, 2013 at 17:42
Missing the unsigned bit?

Ed Heal
– Ed Heal

2013-04-11 17:43:21 +00:00
Commented Apr 11, 2013 at 17:43 — Ed Heal
– Ed Heal, Commented Apr 11, 2013 at 17:43

Kermit · Accepted Answer · 2013-04-11 17:51:46Z

4

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

edited Apr 11, 2013 at 17:51

Kermit

34.1k13 gold badges89 silver badges121 bronze badges

answered Apr 11, 2013 at 17:42

user229044♦

241k41 gold badges347 silver badges350 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bill Karwin · Accepted Answer · 2013-04-11 17:54:29Z

1

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)
);

answered Apr 11, 2013 at 17:54

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Collectives™ on Stack Overflow

Syntax error when creating table in mysql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related