Two tables that they have foreign keys pointing to each other

  • EamonSQL

    SSCrazy

    Points: 2285

    Hi,
    I have an unusual problem in that I have to remove records from two tables that they have foreign keys pointing to each other.
    Silly I know but that's how it's been designed and I can't drop the constraints in order to delete the records.

    If I try deleting records from either table I get a foreign key constraint error message.

    If there's any advice out there around removing these records without disabling the constraints I would appreciate it.
    If this isn't clearly explained please let me know.
    Thanks,
    Emaon

  • Joe Torre

    SSChampion

    Points: 10223

    Try doing both in a single transaction:

    begin tran
       delete tbl1 where ...
       delete tbl2 where...
    commit;

  • drew.allen

    SSC Guru

    Points: 76411

    Joe Torre - Thursday, February 1, 2018 3:16 PM

    Try doing both in a single transaction:

    begin tran
       delete tbl1 where ...
       delete tbl2 where...
    commit;

    If this doesn't work and one (or both) of the fields is nullable, update the field to NULL, delete the record from the other table, and then delete the original record.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA


    How to post data/code on a forum to get the best help[/url].How to Post Performance Problems[/url]

  • Jeffrey Williams 3188

    SSC Guru

    Points: 87950

    If you want to delete the rows that are related - you could modify the tables to use ON DELETE CASCADE.  However, if you do not want to delete the related rows you are going to have to update the foreign keys to a different value then delete the rows.

    That is - on table 1 update the rows with value 'x' to value of 'y' and delete from table2 the rows that have the value 'x'.  Then update the values in table2 with value 'a' to value 'b' - then delete from table1 the rows that have value 'a'.

    Before doing any of this - I would recommend that you copy the data in both tables so you can put the data back if something doesn't work right.

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Joe Torre - Thursday, February 1, 2018 3:16 PM

    Try doing both in a single transaction:

    begin tran
       delete tbl1 where ...
       delete tbl2 where...
    commit;

    Won't work. Constraints are checked at the statement level. The first statement will violate the constraints and fail. The second will then violate the constraints and fail as well.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • EamonSQL

    SSCrazy

    Points: 2285

    Hi everyone,

    much belated thanks for all your help on this.

    I tried various things but the approach that worked for me was "update the field to NULL, delete the record from the other table, and then delete the original record."
    Post reply
    Cheers,
    Eamon

  • RonKyle

    SSC-Dedicated

    Points: 31417

    Just for curiosity sake, are you able and allowed you tell what the tables are.  In all my design years, I can't recall something like this.  I would just like to see it so I can add it to my bag of design tips.  Thanks,

  • Jonathan AC Roberts

    SSCoach

    Points: 16459

    Have you tried updating the FK column values to NULL before deleting the rows?

  • RonKyle

    SSC-Dedicated

    Points: 31417

    Have you tried updating the FK column values to NULL before deleting the rows?

    He already stated that this was the approach that worked for him.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply