MySQL syntax help

[hiya1992]

Hiya all, i keep getting a
"Token line error=7, Token line offset=1, Token in error=CONSTRAINT" error message.

Using this code

Create Table Orders(
orderNumber bigint Not Null,
orderDate datetime Not Null,
completed bit Not Null,
discount numeric(2,1),
PRIMARY KEY(orderNumber)
CONSTRAINT FK_Orders_Store
FOREIGN KEY(StoreID) REFERENCES Store(StoreID)
CONSTRAINT FK_Orders_Pizza
FOREIGN KEY(itemCode) REFERENCES Pizza(itemCode)
)

(the below codes are seperate tables)

Create Table Store(
StoreID int IDENTITY,
town nvarchar(25) NOT NULL,
address nvarchar(50) NOT NULL,
PRIMARY KEY(StoreID)
)

Create Table Pizza(
itemCode int IDENTITY, 
pizzaName nvarchar(25) NOT NULL,
baseType nvarchar(20) NOT NULL,
PRIMARY KEY(itemCode)
)

Any help?

[zergi]

Put the comma before the CONSTRAINT keyword as it starts a new part of the CREATE TABLE query.

Also, make sure in

CONSTRAINT FK_Orders_Store

FK_Orders_Store - is unique across the whole database.

[guelphdad]

You are missing the columns storeid and itemcode in the Orders table.
You actually have to create them in order to make a foreign key on them.

there is also no data type of IDENTITY in mysql. I believe that is Oracle or SQL Server that supports that type.

[hiya1992]

hey thanks that works. I'm getting another CONSTRAINT error message though 0_o on the new codes below. I'm using webmatrix to design these by the way.

Create table Bases(
baseType nvarchar(25) NOT NULL,
basePrice numeric(6,3) NOT NULL,
calories bigint NOT NULL,
fat nvarchar(10) NOT NULL,
salt nvarchar(10) NOT NULL, 
PRIMARY KEY(basetype)
)

-
(Token line number =6, token line offset=1, token in error=constraint)

Create Table Pizza(
itemCode int IDENTITY, 
pizzaName nvarchar(25) NOT NULL,
baseType nvarchar(25),
PRIMARY KEY(itemCode)
CONSTRAINT FK_Pizza_Bases
FOREIGN KEY(baseType) REFERENCES Pizza(baseType)
)

[Old Pedant]

AGAIN, you are missing the COMMA!

You must have a COMMA after every declaration in the CREATE TABLE.

By the by, this is true in MySQL, SQL Server, Access, and most other DBs.

It would help if you would realize *WHERE* the declarations start and begin.

Thus:

Create Table Pizza(
    itemCode int IDENTITY, 
    pizzaName nvarchar(25) NOT NULL,
    baseType nvarchar(25),
    PRIMARY KEY(itemCode),
    CONSTRAINT FK_Pizza_Bases FOREIGN KEY(baseType) REFERENCES Pizza(baseType)
)

The lines in red and magenta show you the separate element declarations in that statement.

And by the by, this *IS* the MySQL forum. Your question is not about MySQL.