Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    4

    Smile Unanswered: Does mySQL supports varchar as Primary key?

    I had been facing problems in creating mysql using foreign key.
    The problems falls on the userID foreign in Combination. They prompt me error (Error 1064: You have an error in your sql syntex. Check the manual that corresponds to your mySQL server version for the right syntex use near 'User(userID) ON UPDATE CASCADE ON DELETE CASCADE) TYPE=INNODB'). This problem does not occur when i create other tables. Thus it should not be syntex error. I would appretiate if somebody could help me to solved it. Thank alot. =)
    =========================================
    CREATE TABLE User
    (
    userID VARCHAR(255) NOT NULL,
    firstName VARCHAR(255),
    lastName VARCHAR(255),
    shipAdd TEXT,
    userStatus VARCHAR(255),
    pswd VARCHAR(8),
    dateJoined INT,
    PRIMARY KEY(userID)) TYPE=INNODB;

    CREATE TABLE Combination
    (
    coID INT NOT NULL AUTO_INCREMENT,
    itemID INT NOT NULL,
    userID VARCHAR(255),
    PRIMARY KEY(coID),
    INDEX(itemID),
    INDEX(userID),
    FOREIGN KEY(itemID) REFERENCES Items(itemID) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY(userID) REFERENCES User(userID) ON UPDATE CASCADE ON DELETE CASCADE) TYPE=INNODB;
    =========================================

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i have never created innodb tables

    your syntax looks okay

    quite possibly USER is a reserved word

    i would rename that table, or else always refer to it in backticks, e.g.

    ... FOREIGN KEY(userID) REFERENCES `User`(userID)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2004
    Posts
    4

    Smile

    Originally posted by r937
    i have never created innodb tables

    your syntax looks okay

    quite possibly USER is a reserved word

    i would rename that table, or else always refer to it in backticks, e.g.

    ... FOREIGN KEY(userID) REFERENCES `User`(userID)
    ==============================================

    Oh i got it! Thanx alot! I had been solving this question for the past three hours. Thanx!

Posting Permissions

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