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

    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.

    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
    Toronto, Canada
    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 | @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