MySQL syntax help

Dev Shed Forums Sponsor:

November 29th, 2012, 06:43 PM

hiya1992

Registered User

Join Date: Nov 2012

Posts: 2

Time spent in forums: 5 h 56 m 45 sec
Reputation Power: 0

MySQL syntax help

2 SQL codes below. Keep getting an error on the second one.

Code:

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

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

Code:

Original - Code
Create Table Order( orderNumber int PRIMARY KEY, orderDate datetime Not Null, completed bit Not Null, discount numeric(2,1) Not Null, CONSTRAINT FK_Order_StoreID FOREIGN KEY(StoreID) REFERENCES (StoreID) )

Create Table Order(
orderNumber int PRIMARY KEY,
orderDate datetime Not Null,
completed bit Not Null,
discount numeric(2,1) Not Null,
CONSTRAINT FK_Order_StoreID
FOREIGN KEY(StoreID) REFERENCES (StoreID)
)

November 29th, 2012, 08:29 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,373

Time spent in forums: 3 Months 1 Week 2 Days 6 h 54 m 33 sec
Reputation Power: 4140

you'll get several errors if you actually run those statements in mysql

in the first one, IDENTITY is invalid for mysql

in the second one, ORDER is a reserved word, so you cannot name your table this without escaping it with backticks, and the foreign key declaration won't work because you forgot to include the StoreID column in the table, and you also didn't say which table it references

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

November 29th, 2012, 08:44 PM

hiya1992

Registered User

Join Date: Nov 2012

Posts: 2

Time spent in forums: 5 h 56 m 45 sec
Reputation Power: 0

Quote:

Originally Posted by r937

Thankyou, sorry i did notice that stupid slip up for the table referencing. I'm not sure what you mean by the IDENTITY being invalid. I hate to bother you further but is there an easy explanation for that?

I'm still getting a CONSTRAINT error as well using the below codes.

Code:

Original - 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) )

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)
)

Code:

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

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

Code:

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

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

November 30th, 2012, 02:50 AM

swampBoogie

Contributing User

Join Date: Jan 2003

Location: Paris Uppland

Posts: 2,349

Time spent in forums: 1 Month 4 Days 7 h 27 m 6 sec
Reputation Power: 390

If you get an error, post the complete error message.

November 30th, 2012, 02:57 AM

shammat

Contributing User

Join Date: Oct 2003

Location: Germany

Posts: 2,685

Time spent in forums: 1 Month 3 Weeks 3 Days 19 h 53 m 13 sec
Reputation Power: 284

And please read the manual before proceeding:

http://dev.mysql.com/doc/refman/5.5/en/create-table.html

__________________
I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
http://forums.devshed.com/misc.php?do=bbcode#code

Tips on how to ask better questions:
http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
http://wiki.postgresql.org/wiki/SlowQueryQuestions
http://catb.org/esr/faqs/smart-questions.html

November 30th, 2012, 04:26 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,373

Time spent in forums: 3 Months 1 Week 2 Days 6 h 54 m 33 sec
Reputation Power: 4140

Quote:

Originally Posted by hiya1992

I'm still getting a CONSTRAINT error as well using the below codes.

because you're trying to declare a foreign key for a column that you have not included in the table

wrong --

CREATE TABLE wrong
( foo INTEGER
, bar INTEGER
, CONSTRAINT x FOREIGN KEY (fap) REFERENCES humpty ( dumpty )
)

right --

CREATE TABLE right
( foo INTEGER
, bar INTEGER
, fap INTEGER
, CONSTRAINT x FOREIGN KEY (fap) REFERENCES humpty ( dumpty )
)

MySQL syntax help

Developer Shed Advertisers and Affiliates