Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    4

    Unanswered: mysql foreign key troubles

    Hi
    Could anyone tell me what is wrong below?:

    create table USER
    (
    userID BIGINT UNSIGNED AUTO_INCREMENT,
    lastName VARCHAR(200) NOT NULL,
    firstName VARCHAR(200) NOT NULL,
    office VARCHAR(200) NOT NULL,
    home VARCHAR(200) NOT NULL,
    tel VARCHAR(200) NOT NULL,
    mob VARCHAR(200) NOT NULL,
    email VARCHAR(200) NOT NULL,
    fax VARCHAR(200) NOT NULL,
    PRIMARY KEY(userID)
    )TYPE=InnoDB;

    create table USER_PHOTO
    (
    userID BIGINT UNSIGNED NOT NULL,
    photoName VARCHAR(200) NOT NULL,
    photoWidth INT UNSIGNED,
    photoHeight INT UNSIGNED,
    PRIMARY KEY (userID,photoName),
    FOREIGN KEY(userID) REFERENCES USER(userID)
    )TYPE=InnoDB;

    i get an an error:
    ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'USER(userID)
    )TYPE=InnoDB' at line 8

    cheers
    bart

  2. #2
    Join Date
    Nov 2003
    Posts
    91
    This works:

    create table USER2
    (
    userID INT(4) UNSIGNED AUTO_INCREMENT,
    lastName VARCHAR(200) NOT NULL,
    firstName VARCHAR(200) NOT NULL,
    office VARCHAR(200) NOT NULL,
    home VARCHAR(200) NOT NULL,
    tel VARCHAR(200) NOT NULL,
    mob VARCHAR(200) NOT NULL,
    email VARCHAR(200) NOT NULL,
    fax VARCHAR(200) NOT NULL,
    PRIMARY KEY(userID)
    )TYPE=InnoDB;

    create table USER_PHOTO
    (
    userID INT(4) UNSIGNED NOT NULL,
    photoName VARCHAR(200) NOT NULL,
    photoWidth INT UNSIGNED,
    photoHeight INT UNSIGNED,
    INDEX userID_idx (userID),
    PRIMARY KEY (userID,photoName),
    FOREIGN KEY(userID) REFERENCES USER2(userID)
    )TYPE=InnoDB;

    MySQL gets confused when you name your table 'USER'
    because MySQL already has a USER table in the mysql database.

  3. #3
    Join Date
    Dec 2003
    Posts
    4
    thanks much

Posting Permissions

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