dcsimg
Results 1 to 6 of 6

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

  1. #1

    Thread Starter
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,924

    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

    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?
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  2. #2
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,743

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

    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?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  3. #3
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Java (JSP) Hell!
    Posts
    5,293

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

    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?

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  4. #4

    Thread Starter
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,924

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

    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.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  5. #5

    Thread Starter
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,924

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

    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.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  6. #6
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Java (JSP) Hell!
    Posts
    5,293

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

    cool (post #4)

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width