>>>>Hi,
>>>>
>>>>I've got a SQL Server Express 2008 R2 database which has a Person table where I store People and their dependents like this:
>>>>
>>>>PersonId
>>>>Name
>>>>ParentId
>>>>
>>>>So one PersonID can have many dependents by storing their ID in the ParentID field (simple self join).
>>>>
>>>>I want to set up referential Integrity on this table so that if a parent is deleted, their dependents are deleted (cascade). I created a database diagram, dropped my Person table on it and then dragged the PersonId field to the ParentID field. However, when I try to set the Delete Rule to Cascade, it is disabled.
>>>>
>>>>How can I do this?\
>>>
>>>I don't believe such complex RI is supported. Most likely you need to implement this RI through the trigger.
>>
>>It is supported. My first guess is you set the relation the wrong way (wrong table has the FK field). I'll verify in a few minutes. Also make sure your primary key is set to PersonID in the Person table.
>
>Hi William,
>
>I've tried dragging both ways and can't access the delete rule either way. I hope you can tell me what I've missed.
I reread your initial message - I didn't realize you were using a single table with a relation set to itself.
Naomi may be right - this might not be possible using the RI designer. I'm checking..
____________________________________
Don't Tread on Me
Overthrow the federal government NOW!
____________________________________