The DELETE statement conflicted with the REFERENCE constraint... - which record?

I'm pretty sure what I want can't be done but I really want it so I'm posting in blind hope:rolleyes:

I'm building a purge process to support GDPR for a client. This involves (among other things) copying aged data into a locked down database and then deleting it from the main production database. The data is spread across multiple tables which have Foreign Key constraints in place.

My problem is when I come to do the delete. I delete aged data from a child table then from the parent table. Occasionally, a new insert into the child could interleave creating a new child record against the, yet to be deleted, parent. This will cause the Parent deletion to fail due to the Foreign Key constraint. This isn't a major problem as the whole thing can be investigated and, if necessary, recovered into the production database from the locked down database. I do, however, need to report it happening to the DBAs so that they know there's something that needs to be investigated.

I'd like to be able to report the Id (or whatever the primary key value is) of the offending parent if I can. But the error message doesn't include that information (in full it reads [The DELETE statement conflicted with the REFERENCE constraint "fk_Child_Parent". The conflict occurred in database "Purge", table "dbo.Child", column 'ParentId'.])

The DBAs could, of course, throw together a query to find any record that would violate the constraint if it were deleted but I'd rather save them the work. It just sorta feels like I should be able to recover it at the point of error. Any suggestions on how I can identify the culprit?

Just to head some possible suggestions (which I've already considered) off at the pass:-
1. This DB is in HEAVY use, is web facing and the deletes are big enough to take a significant amount of time. Wrapping things up in a transaction isn't an option because we can't block activity on the web page.
2. Cascading deletes (or anything similar) isn't an option because the interleaved child record wouldn't have been copied to the locked down DB so wouldn't be recoverable.

So really, I just want that ID. Any thoughts?

I do not think you can get at the id values you want to based on the error - at that error moment.

If all the time is really related to the child delete's can you not - after those child deletes - start a TRANSACTION.

In that TRANSACTION you attempt to delete child records again - should get 0 - might find some of your strays

And the final step in that TRANSACTION you delete the parent records. Then release the lock.

How are you "holding" the key's that need to be deleted from the CHILD DELETE to the PARENT DELETE? Are you simply using some WHERE clause on the parent?

Is this in a STORED PROCEDURE?

You have a list of the 'aged' data, couldnt you just connect to the child and see which return ? Those that dont can be deleted, the rest have to be left for which ever reason. At the end you link your list to be deleted with those that are left and return a report?

Quote:

---

I do not think you can get at the id values you want to based on the error

---

I didn't think so, apart from anything there could be several violating records while an error message could only hold one.

I've got a solution for now. I'm parsing the foreign key out of the error text. I've then got an if statement that branch into a set of clauses where I select the parent ids from the parent joined to the child. Anything I get has children so would cause the violation - tack the list onto my error string and email it off.

It's pretty complicated to look at but is actually a fairly simple once you understand it. The worry would be that a new FK gets added later so won't have a branch in my IF. But since the list of violating records is only really a "nice to have" I think I can live with that.

Quote:

---

couldnt you just connect to the child and see which return ?

---

cross posted with you. Yeah that's basically what I'm doing to build the error message. I can't use it to do the actual deletion because I couldn't guarantee that all children had been written to the lock down DB but it's good enough for the error message.

cool (post #4)