CODEWITHSUNDEEP
sql-serverdatabasedatabase-design
Raidil142
Raidil142

Reputation: 137

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 either 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

Upvotes: 1

Views: 95

Answers (3)

p.campbell
p.campbell

Reputation: 100607

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

Upvotes: 3

Damir Sudarevic
Damir Sudarevic

Reputation: 22187

enter image description here

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.)

Upvotes: 1

Icarus
Icarus

Reputation: 63962

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.

Upvotes: 0

Related Questions