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

SQL won't let a FK constraint have a DELETE action when it points to the same table - it opens the possibility of a cyclic relation. Makes sense.

I think your trigger is the safer way to go..