FK that comes from two different tables?

Databases
1

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

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

:smiley:

3