#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    2
    Rep Power
    0

    MySQL syntax help


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

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





    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)
    )
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,261
    Rep Power
    4279
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    2
    Rep Power
    0
    Originally Posted by r937
    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
    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:
    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:
    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)
    )
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,385
    Rep Power
    391
    If you get an error, post the complete error message.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,787
    Rep Power
    349
    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
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,261
    Rep Power
    4279
    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 )
    )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo