Creating relationship between column and replacement column

Hello,
I have a table (tblManchineParts) that is the list of all machine parts.
The second table (tblReplacementParts) is a list of all the machine parts
and the machine part that replaced it. I tried to create two foreign key
relationships to enforce Referential Integrity:
tblMachineParts.MachinePart_ID and tblReplacementParts.MachinePart_ID
and
tblMachineParts.MachinePart_ID and tblReplacementParts.ReplacementPart_ID
When I did this, I got the error: Introducing second foreign key may cause
cycles or multiple cascade paths. My question is, how do I enforce
referential integrity on both the fields in the table tblReplacementParts
since their values have to be in the table tblMachineParts?
--
Thanks in advance,
Steven
tblMachineParts
--
MachinePart_ID (varchar 255)
tblReplacementParts
--
MachinePart_ID (varchar 255)
ReplacementPart_ID (varchar 255)
examples
(tblMachineParts)
MachinePart_ID
--
Part01
part02
Part03
(tblReplacementParts)
MachinePart_ID ReplacementPart_ID
-- --
Part02 Part03Hi, Steven
You can enforce referential integrity, you just can't use cascade
updates. For more informations, see this thread:
http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_frm/thread/a802401b5d653f00
Razvan