[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 305
  • Last Modified:

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?
0
FairyBusiness
Asked:
FairyBusiness
  • 3
  • 2
2 Solutions
 
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
 
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now