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)