How do I make a table where one of two foreign keys are required?

I'm creating a table of ownerships for files. A file can be owned by either a single user (table Users) or a group of users (table UserGroups). I would like to make this whole relationship as one table, where each tuple is ei开发者_运维技巧ther a group's ownership or a user's ownership of a file.

Users: User_ID(PK), name, email

UserGroups: UserGrp_ID(PK), name, creator_ID(FK to Users)

TestGroupOwnerships: ???

I think it's possible to do both relationships as one table, but I'm not exactly sure how. It is also acceptable if Users or UserGroups tables need to change (still in the planning stage).

Thanks,

Alex

You could:

• allow NULL on both FK columns
• add a CHECK CONSTRAINT to ensure that one of them is non-null:

ALTER TABLE dbo.MyTable ADD CONSTRAINT
    CK_MyTable CHECK (Column1 IS NOT NULL OR Column2 IS NOT NULL)
GO

Notes

1. OwnerID = PartyID (OwnerId is a role name of PartyID in the File table).
2. UserID = PartyID (UserID is a role name of PartyID in the User table.)
3. GroupID = PartyID (GroupID is a role name of PartyID in the Group table.)

How about:

Have a table with (File,OwnerID (int),OwnerType(Char(1))

Where OwnerID can either be UserID or GropuID and OwnerType can either Be ('G' - Group or 'U' - User)

Or....

Have a table with (File,OwnerID,GroupID) where a touple that has OwnerID populated joins to the user table and a tuple with GroupID populated joins to the Groups table.