Skip to main content

Solution for BI Error "The DELETE statement conflicted with the REFERENCE constraint "FK_fact_item_warranties_fact_items""

  • September 24, 2021
  • 1 reply
  • 126 views

Forum|alt.badge.img+10

Recently, we’ve had a few issues in which the BI Cube is red, meaning the BI Increment Load failed for some reason.  If the reason for failure is the following error, we have a common data fix to correct the issue.

Error: The DELETE statement conflicted with the REFERENCE constraint "FK_fact_item_warranties_fact_items". The conflict occurred in database "AsteaBIProduction_DW", table "dbo.fact_item_warranties", column 'dim_item_key'.

 

To fix this issue, first run the following select query on the Data Warehouse database to check if there are any records where the referenced item ID doesn’t exist:

select * from fact_item_warranties where fact_item_warranties.dim_item_key in (select item_id from item_Deleted);

If the above query returns any results, please run the following query against the same Data Warehouse database:

update fact_item_warranties set dim_item_key = -1 where fact_item_warranties.dim_item_key in (select item_id from item_Deleted);

If the above mentioned error is the only error in the log, after the data is corrected, the next scheduled incremental load should run successfully.

Did this topic help you find an answer to your question?

Forum|alt.badge.img+10

A similar issue was reported for a customer where the error was: “The DELETE statement conflicted with the REFERENCE constraint "FK_fact_pm_schedule_fact_items". The conflict occurred in database "AsteaBIHCI_DW", table "dbo.fact_pm_schedule", column 'dim_item_key'.”

 

This case can be identified with the following select statement:

select * from fact_pm_schedule where fact_pm_schedule.dim_item_key in (select item_id from item_Deleted);

And resolved by updating the invalid reference to -1 with this query:

update fact_pm_schedule set dim_item_key = -1 where fact_pm_schedule.dim_item_key in (select item_id from item_Deleted);


 


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings