Deleting child table record delete associated parent table record
Using SQL Server 2000.
I have one parent table and a child table. At one time there was a Cascade Delete relationship between the parent's primary key and the child's associated foreign key.
When a child table record was deleted, its associated parent table record was also deleted. This deletion was unwarranted. Hence, I have removed any relationship between the parent primary key and the child foreign key. Yet, when deleting another child table record, the parent table record is still being deleted.
Any suggestions?
I have one parent table and a child table. At one time there was a Cascade Delete relationship between the parent's primary key and the child's associated foreign key.
When a child table record was deleted, its associated parent table record was also deleted. This deletion was unwarranted. Hence, I have removed any relationship between the parent primary key and the child foreign key. Yet, when deleting another child table record, the parent table record is still being deleted.
Any suggestions?
Can you script these two tables with all the constraints and see still any relation exist....Just to double check you cleared the relations.
Are you sure you don't have that back to front?
A normal cascade delete will delete the child when the parent record is deleted, not the other way round. If you do have a scenario whre the parent record is deleted whenever the child record is delted, then this will be enforced by a custom trigger, not by a cascade delete. Check if you have such a trigger on the child table.
A normal cascade delete will delete the child when the parent record is deleted, not the other way round. If you do have a scenario whre the parent record is deleted whenever the child record is delted, then this will be enforced by a custom trigger, not by a cascade delete. Check if you have such a trigger on the child table.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Indeed! There was a trigger set to delete the corresponding parent table record when the child table record was deleted. I will edit the script in Enterprise Manager and remove the deletion (there is other code which I wish to retain). Thank you for your help.
You're welcome.