Posted by arzewski on 12/08/05 17:12

I have a situation where attending a meeting could be either staff or
coalition members. But don't know how to enforce a foreign key
constraint. any ideas ?

Table Meeting
MeetingID int NOT NULL,
AttendeeID int NOT NULL
Primary Key (MeetingID, AttendeeID)

Table Staff
StaffID int IDENTITY not null PRIMARY KEY

Table CoalitionMember
MemberID int Identity not null PRIMARY KEY

Since AttendeeID can either a value from Staff.StaffID or from
CoalitionMember.MemberID, I cannot place both constraints as

ADD CONSTRAINT [FK_Meeting_Staff] FOREIGN KEY
(AttendeeID)
REFERENCES [Staff] ([StaffID])

ADD CONSTRAINT [FK_Meeting_CoalitionMember] FOREIGN KEY
(AttendeeID)
REFERENCES [CoalitionMember] ([MemberID])

[Back to original message]