SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Two tables that they have foreign keys pointing to each other


Two tables that they have foreign keys pointing to each other

Author
Message
EamonSQL
EamonSQL
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1889 Visits: 287
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
Joe Torre
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7782 Visits: 1402
Try doing both in a single transaction:

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


drew.allen
drew.allen
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63938 Visits: 17003
Joe Torre - Thursday, February 1, 2018 3:16 PM
Try doing both in a single transaction:
[code]
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.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
Jeffrey Williams 3188
Jeffrey Williams 3188
SSC Guru
SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)

Group: General Forum Members
Points: 77540 Visits: 11170
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.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

GilaMonster
GilaMonster
SSC Guru
SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)

Group: General Forum Members
Points: 902794 Visits: 48740
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
EamonSQL
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1889 Visits: 287
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
RonKyle
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26935 Visits: 4466
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
Jonathan AC Roberts
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7825 Visits: 3644
Have you tried updating the FK column values to NULL before deleting the rows?
RonKyle
RonKyle
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26935 Visits: 4466
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.




Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search