Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New to the CF scene
    Join Date
    Nov 2012
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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?
    Last edited by hiya1992; 11-30-2012 at 06:40 PM.

  • #2
    New to the CF scene
    Join Date
    Nov 2012
    Posts
    9
    Thanks
    1
    Thanked 2 Times in 2 Posts
    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.

  • #3
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

  • The Following 2 Users Say Thank You to guelphdad For This Useful Post:

    hiya1992 (11-30-2012), zergi (11-30-2012)

  • #4
    New to the CF scene
    Join Date
    Nov 2012
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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)
    )

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,133
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •