Error when running MYSQL code--Super confused

Question

I get this error when trying to compile in sqlfiddle- 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 ')' at line 9

If I take Line 9 out it does the same, I'm not sure how to fix this.

I'm pretty new to this, can I run these commands from the community edition of MySQL?

If I run the customer table by itself it works, I add the Pizza table it's fine, but when I add the OrderInformation table is where I start getting that error. I've gone through the code a few times, is there something I'm missing when it comes to the orderinformation table? I'm Stuck.

CREATE TABLE Customer 
( 
CustomerID     int                 NOT NULL   AUTO_INCREMENT, 
FirstName      varchar(45)   NOT NULL, 
LastName       varchar(45)  NOT NULL, 
StreetAddress  varchar(45)  NOT NULL, 
City                    varchar(45)  NOT NULL, 
State                 varchar(2)   NOT NULL, 
ZipCode            varchar(5)     NOT NULL, 
HomePhone    INT(11)  , 
MobilePhone   INT(11) , 
OtherPhone     INT(11), 
PRIMARY KEY (CustomerID) 
); 



CREATE TABLE Pizza 
( 
PizzaID          INT                   NOT NULL   AUTO_INCREMENT, 
PizzaName   VARCHAR(45) NOT NULL, 
Description    VARCHAR(90), 
UnitPrice        DECIMAL           NOT NULL, 
PRIMARY KEY (PizzaID) 
); 


CREATE TABLE OrderInformation 
( 
OrderID             int          NOT NULL, 
CustomerID      int          NOT NULL  AUTO_INCREMENT, 
OrderDate        date       NOT NULL, 
PRIMARY KEY (CustomerID, OrderID), 
FOREIGN KEY(CustomerID) REFERENCES  Customer (CustomerID), 

);


CREATE TABLE OrderItem 
( 
OrderID     INT              NOT NULL, 
Quantity   SMALLINT(5), 
PizzaID   INT               NOT NULL, 
PRIMARY KEY (OrderID, PizzaID), 
FOREIGN KEY(PizzaID) REFERENCES Pizza (PizzaID), 
FOREIGN KEY(OrderID) REFERENCES  OrderInformation (OrderID) 
);

When I run my code in SQL fiddle it will give me this 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 ')' at line 9 Sometimes I can mess with it and get it to go away and then it gives me a message about the OrderInformation table not liking the foreign key. — Nippon87
– Nippon87, Commented Oct 20, 2016 at 12:14

Jorge Campos · Accepted Answer · 2016-10-21 01:16:00Z

1

Your problem is that you left a comma , where it shouldn't be here:

CREATE TABLE OrderInformation 
( 
OrderID             int          NOT NULL, 
CustomerID      int          NOT NULL  AUTO_INCREMENT, 
OrderDate        date       NOT NULL, 
PRIMARY KEY (CustomerID, OrderID), 
FOREIGN KEY(CustomerID) REFERENCES  Customer (CustomerID), --<--------- here
);

Remove it and you should be fine.

There is also another problem I noticed.

On the OrderItem table you tried to add a foreign key constraint to OrderInformation table, but you only specified one field. The OrderInformation table has two fields as primary key so every table that has a constraint to it should also have both columns.

Also, make no sense to have CustomerID int NOT NULL AUTO_INCREMENT, column as AUTO_INCREMENT in OrderInformation table. It should just be a foreign key constraint. Given your database model.

edited Oct 21, 2016 at 1:16

answered Oct 21, 2016 at 1:10

Jorge Campos

23.9k7 gold badges65 silver badges95 bronze badges

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

Collectives™ on Stack Overflow

Error when running MYSQL code--Super confused

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related