Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Deleting child table record delete associated parent table record

Posted on 2004-11-18
Medium Priority
Last Modified: 2008-02-20
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?
Question by:mic138
LVL 11

Expert Comment

ID: 12622046
Can you script these two tables with all the constraints and see still any relation exist....Just to double check you cleared the relations.

LVL 17

Expert Comment

ID: 12623082
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.
LVL 16

Accepted Solution

muzzy2003 earned 1000 total points
ID: 12623252
In Query Analyzer, open the object explorer (F8), expand the database down to the child table and then expand its triggers. Have a look there, you may find there is a trigger that is doing this delete. Right click, Script to new windows as drop, run the script, and it's gone.

Author Comment

ID: 12625274
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.
LVL 16

Expert Comment

ID: 12625299
You're welcome.

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question