We help IT Professionals succeed at work.

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

FairyBusiness
on
Medium Priority
320 Views
Last Modified: 2012-05-11
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?
Comment
Watch Question

Greg AlexanderLead Developer

Commented:
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

Author

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'
Lead Developer
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Yep that worked!  So why can I not use the foreign key??
CERTIFIED EXPERT
Expert of the Year 2008
Top Expert 2008
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
I see so I must have InnoDB to use a foreign key.Thanks you guys!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.