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

    Unanswered: FK that comes from two different tables?

    I was wondering if there is anyway to have a foreign key that comes from two different tables. Why you ask?

    I have a table "cast" with a primary key of ID and a foreign key of user_id.

    In my database I have two different types of users (registered and unregistered) I would like to continue to keep them separate if possible.

    So I want to know if there is anyway to say that username is a foreign key for either registered.user_id or unresistered_user_id? The user_id for users and unregistered users have different numbering so they will never overlap.

    Code:
    CREATE TABLE `cast` (
      `cast_id` int(11) NOT NULL AUTO_INCREMENT,
      `cast_user_id` int(11) DEFAULT NULL,
      `cast_video_id` int(11) DEFAULT NULL,
      `cast_proj_id` int(11) DEFAULT NULL,
      PRIMARY KEY (`cast_id`),
      FOREIGN KEY(cast_user_id) REFERENCES users(user_id) 
      FOREIGN KEY(cast_user_id) REFERENCES unreg_users(unreg_user_id)
    )

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    keeping registered and unregistered users separate should not be done with separate tables

    rather, you should have only one users table, and a column that indicates status (registered/unregistered)

    then your FK question disappears

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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