Printable View
I am using vb2088 Express edition and I am trying to figure out how to delete a parent record from one table with child records from another table. Right now if I try to execute a delete of the parent records an error occur saying that it's violate the foreign key constraints. Can anyone give me a hand with this?
Right,
The way you can resolve your error is by deleting all records simultaneusly and that can be accomplished using T-SQL.
You didnt indicate which database you are using. If its Access I dont think this can be done but if its SQL Server it can.
Have a look at this site it specifies exactly your problem
Hope it helps
You have two choices:
1. Configure the foreign key relationship to cascade deletions.
2. Manually delete all child records before deleting a parent.
The first option is easier. Once it's done you just go ahead and delete the parent record and all the child records will be automatically deleted. Alternatively you can do as angelica suggests.
Pesonnaly as a DBA I always agrue against the first optiom. I always recommend doing the deletes required by code.
I certainly have heard that sentiment and, in fact, nearly mentioned it in my previous post. I'd be interested to hear the specific rationale behind it. I figure that if you're just going to write the code to delete all the child records with a specific parent ID anyway then you may as well just let the relationship do it for you. That said, there are plenty of things that I do a particular way, and recommend to others to do the same way, because it's "the right way" to do it. This may well be one of those, or there may be implications I haven't considered. I'd be interested to hear an explanation from someone who knows.Quote:
Originally Posted by GaryMazzone
I have worked many positions where delete of data was almost always forbiden (gov (military) conctract, financial, Nuclear power plants). If a delete is to be performed on parent data we always require a reponse in the affirmitave that the delete is required. If the answer is yes we would code the delete of all required tables in stored procs or in the code itself. If someone did a delete on the database the keys will keep the delete from happening by accident.
In fact more often then not the delete was not a phsyical delete but a locgical delete only (the record had a field to mark it as logically deleted).
Thank you everyone for your input. I am using a Ms SQL DB. I am just writing a simple POS system. jmcilhinney, for option 1 "Configure the foreign key relationship to cascade deletions" can you tell me how I do that is there a sample code I can look at? Thank you again.
If you do want to take that route then you'd normally do it in SQL Server Management Studio. You could execute the appropriate SQL code but I'd generally do it visually. Open the table in the designer, open the relations dialogue, select the relation of interest and configure it to cascade deletes.Quote:
Originally Posted by ronan9715