XOR and foreign key constraints

[arzewski]

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

[David Portas]

ar******@hotmail.com wrote:

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

Please do not multi-post!
This has been answer in microsoft.public.sqlserver.programming

--
David Portas
SQL Server MVP
--