CodingForums.com - MySQL syntax help

CodingForums.com (http://www.codingforums.com/index.php)

- MySQL (http://www.codingforums.com/forumdisplay.php?f=7)

- - MySQL syntax help (http://www.codingforums.com/showthread.php?t=283274)

hiya1992

11-30-2012 03:54 AM

MySQL syntax help

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

Using this code

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)

Code:

Create Table Store(

StoreID int IDENTITY,

town nvarchar(25) NOT NULL,

address nvarchar(50) NOT NULL,

PRIMARY KEY(StoreID)

)

Code:

Create Table Pizza(

itemCode int IDENTITY, 

pizzaName nvarchar(25) NOT NULL,

baseType nvarchar(20) NOT NULL,

PRIMARY KEY(itemCode)

)

Any help?

zergi

11-30-2012 08:45 AM

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

Also, make sure in

Code:

CONSTRAINT FK_Orders_Store

FK_Orders_Store - is unique across the whole database.

guelphdad

11-30-2012 03:32 PM

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

11-30-2012 06:42 PM

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.

Code:

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)

Code:

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

11-30-2012 08:28 PM

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:

Code:

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.

All times are GMT +1. The time now is 06:47 AM.