• Community
  • Groups
  • Alliance
  • Solution for BI Error "The DELETE statement conflicted with the REFERENCE constraint "FK_fact_item_warranties_fact_items""

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

  • 24 September 2021
  • 1 reply
  • 94 views
A
Rank
Userlevel 7
Badge +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.

1 reply

A
Rank
Userlevel 7
Badge +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


Terms & ConditionsCookie settings