Results 1 to 2 of 2
Mar 8, 2012, 10:17 #1
- Join Date
- Dec 2011
- 0 Post(s)
- 0 Thread(s)
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) )
Mar 8, 2012, 11:14 #2
- Join Date
- Jul 2002
- Toronto, Canada
- 52 Post(s)
- 2 Thread(s)
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