Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2012
    Posts
    10

    Unanswered: 2 foreign keys referencing the same field

    Can I have 2 foreign keys in a table reference the same field in another table?
    Here is the table i'm trying to create, but I keep getting a syntax error. If yes do you see any other seasons I would be getting a syntax error?
    Code:
    CREATE TABLE `reg_user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `unreg_user_id` int(11) NOT NULL,
      `reg_user_id` int(11) NOT NULL,
      `reg_user_cast_id` int(11) NOT NULL,
      PRIMARY KEY (`id`),
      FOREIGN KEY('unreg_user_id') REFERENCES users(user_id),
      FOREIGN KEY('reg_user_id') REFERENCES users(user_id),
      FOREIGN KEY('reg_user_cast_id') REFERENCES cast(cast_id)
    );

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Look closely at your FOREIGN KEY definitions you are using single quote instead of backtick.

    Also the word CAST is a reserved word in MYSQL so you will need to also include a backtick with that:

    Code:
    CREATE TABLE `reg_user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `unreg_user_id` int(11) NOT NULL,
      `reg_user_id` int(11) NOT NULL,
      `reg_user_cast_id` int(11) NOT NULL,
      PRIMARY KEY (`id`),
      FOREIGN KEY(`unreg_user_id`) REFERENCES users(user_id),
      FOREIGN KEY(`reg_user_id`) REFERENCES users(user_id),
      FOREIGN KEY(`reg_user_cast_id`) REFERENCES `cast`(cast_id)
    );
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Feb 2012
    Posts
    10
    thanks that fixed it.

Posting Permissions

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