0

I am working with loading XML files contents into a table in a SQL Server database, for which I wanted to delete the contents of a table dbo.Accounting in the SQL Server database, which has APC_PK_ID as its primary key, and then load back its contents from the xml file.

But there was this message that showed up saying:

The DELETE statement conflicted with the REFERENCE constraint "FK__Inventori__Accou__29CC2871". The conflict occurred in database "PAModel", table "dbo.Inventories", column 'APC_PK_ID'. The statement has been terminated.

So now I figured out that I had to delete the column contents of APC_PK_ID from dbo.Inventories table separately.

But the point of concern for me is, is it possible to restore back the contents of APC_PK_ID in the dbo.Inventories table, as it was before deletion?

Will there be any data loss or problem in loading the data back, if there happens to be a method?

Thank you for checking into the question.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
B21
  • 37
  • 1
  • 9
  • Either you have to drop the foreign key constraint, or set all the values in the FK column to null. – devC Mar 29 '18 at 09:26

2 Answers2

0

This is quite common topic: How can foreign key constraints be temporarily disabled using T-SQL?

Enabling/Disabling constraints will not result to data loss. However, you cannot enable them back if you have data inconsistancy. For instance, child table record refers to a missing record of parent table

Alexander Volok
  • 5,630
  • 3
  • 17
  • 33
0

You don't need to remove (DROP) the foreign key constraint, you can disabled it instead, using NOCHECK CONSTRAINT:

ALTER TABLE dbo.Inventories NOCHECK CONSTRAINT FK__Inventori__Accou__29CC2871

And after the changes, to enable again:

ALTER TABLE dbo.Inventories WITH CHECK CHECK CONSTRAINT FK__Inventori__Accou__29CC2871

Off course, if there's any inconsistent data, you won't be able to enable the constraint

Ricardo Pontual
  • 3,749
  • 3
  • 28
  • 43