>What I think William is referring to and may be easier to implement is Person table - PersonID, Name, other attributes.
>
>PersonRelationship
>
>PersonID ParentID
>
>In this case we should be able to create relations and use referential integrity.
>

I can't do that, it has to be a single table.

Does my trigger look OK?


>
>>>>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.
>>
>>Should this do it:
>>
>>

CREATE TRIGGER PersonsDeleteDependents 
>>   ON  Persons
>>   FOR DELETE
>>AS 
>>BEGIN
>>	-- SET NOCOUNT ON added to prevent extra result sets from
>>	-- interfering with SELECT statements.
>>	SET NOCOUNT ON;
>>
>>    -- Insert statements for trigger here
>>    delete Persons
>>    from Persons
>>    inner join deleted on deleted.personid = persons.parentid
>>
>>END

>>
>>?