What is wrong with the way I am trying to create my table?

Hi, I am trying to create a table:

CREATE TABLE IF NOT EXISTS orders (
 id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 customer_id INT NOT NULL UNSIGNED FOREIGN KEY references customers(id),
 amount FLOAT (6,2),
 date DATE NOT NULL,
 order_status VARCHAR(20) NOT NULL,
 ship_name VARCHAR(255) NOT NULL,
 ship_address VARCHAR(255) NOT NULL,
 ship_city VARCHAR(100) NOT NULL,
 ship_state VARCHAR(20) NOT NULL,
 ship_zip VARCHAR(10) NOT NULL,
 ship_country VARCHAR(50) NOT NULL
);

Open in new window


But I get this error message:

#1064 - 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 'UNSIGNED FOREIGN KEY references customers(id),
amount FLOAT (6,2),
date DATE' at line 3

I have tried a few things but not really sure what its wanting me to do.  Anyone got some suggestions?
FairyBusinessAsked:
Who is Participating?
 
Greg AlexanderLead DeveloperCommented:
Looks to be missing a semicolon

CREATE TABLE IF NOT EXISTS orders (
 id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 customer_id INT NOT NULL references customers(id),
 amount FLOAT (6,2),
 date DATE NOT NULL,
 order_status VARCHAR(20) NOT NULL,
 ship_name VARCHAR(255) NOT NULL,
 ship_address VARCHAR(255) NOT NULL,
 ship_city VARCHAR(100) NOT NULL,
 ship_state VARCHAR(20) NOT NULL,
 ship_zip VARCHAR(10) NOT NULL,
 ship_country VARCHAR(50) NOT NULL
);

CREATE TABLE IF NOT EXISTS order_items (
 id INT UNSIGNED NOT NULL REFERENCES orders(id),
 isbn VARCHAR(13) NOT NULL,
 item_price FLOAT(4,2) NOT NULL,
 quantity TINYINT UNSIGNED NOT NULL,
 PRIMARY KEY (id, isbn)
);

Open in new window

0
 
Greg AlexanderLead DeveloperCommented:
the UNSIGNED FOREIGN KEY is not supported apparently
CREATE TABLE IF NOT EXISTS orders (
 id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 customer_id INT NOT NULL references customers(id),
 amount FLOAT (6,2),
 date DATE NOT NULL,
 order_status VARCHAR(20) NOT NULL,
 ship_name VARCHAR(255) NOT NULL,
 ship_address VARCHAR(255) NOT NULL,
 ship_city VARCHAR(100) NOT NULL,
 ship_state VARCHAR(20) NOT NULL,
 ship_zip VARCHAR(10) NOT NULL,
 ship_country VARCHAR(50) NOT NULL
)

Open in new window

0
 
FairyBusinessAuthor Commented:
Ok I took the foreign keys out (I have two tables actually in my sql file)

CREATE TABLE IF NOT EXISTS orders (
 id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 customer_id INT NOT NULL references customers(id),
 amount FLOAT (6,2),
 date DATE NOT NULL,
 order_status VARCHAR(20) NOT NULL,
 ship_name VARCHAR(255) NOT NULL,
 ship_address VARCHAR(255) NOT NULL,
 ship_city VARCHAR(100) NOT NULL,
 ship_state VARCHAR(20) NOT NULL,
 ship_zip VARCHAR(10) NOT NULL,
 ship_country VARCHAR(50) NOT NULL
)

CREATE TABLE IF NOT EXISTS order_items (
 id INT UNSIGNED NOT NULL REFERENCES orders(id),
 isbn VARCHAR(13) NOT NULL,
 item_price FLOAT(4,2) NOT NULL,
 quantity TINYINT UNSIGNED NOT NULL,
 PRIMARY KEY (id, isbn)
);

Open in new window


but I am still getting an error message:

#1064 - 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 'CREATE TABLE IF NOT EXISTS order_items (
id INT UNSIGNED NOT NULL REFERENCES o'
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
FairyBusinessAuthor Commented:
Yep that worked!  So why can I not use the foreign key??
0
 
hieloCommented:
http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

CREATE TABLE IF NOT EXISTS orders (
 id INT NOT NULL AUTO_INCREMENT,
 customer_id INT NOT NULL,
 amount FLOAT (6,2),
 date DATE NOT NULL,
 order_status VARCHAR(20) NOT NULL,
 ship_name VARCHAR(255) NOT NULL,
 ship_address VARCHAR(255) NOT NULL,
 ship_city VARCHAR(100) NOT NULL,
 ship_state VARCHAR(20) NOT NULL,
 ship_zip VARCHAR(10) NOT NULL,
 ship_country VARCHAR(50) NOT NULL,

 PRIMARY KEY(id),
 FOREIGN KEY (customer_id)
  REFERENCES customer(id)
) ENGINE InnoDB;

CREATE TABLE IF NOT EXISTS order_items (
 id INT UNSIGNED NOT NULL ,
 isbn VARCHAR(13) NOT NULL,
 item_price FLOAT(4,2) NOT NULL,
 quantity TINYINT UNSIGNED NOT NULL,
 PRIMARY KEY (id, isbn),
 FOREIGN KEY (id)
  REFERENCES orders(id)
)ENGINE InnoDB;

Open in new window

0
 
FairyBusinessAuthor Commented:
I see so I must have InnoDB to use a foreign key.Thanks you guys!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.