Load Data Warehouse job fails on dwh_inv_project_fk1 constraint

book

Article ID: 208549

calendar_today

Updated On:

Products

Clarity PPM On Premise Clarity PPM SaaS

Issue/Introduction

Load Date Warehouse job fails with the following error message.

ERROR: ENCOUNTERED EXCEPTION WHILE ENABLING dwh_inv_project_fk1.  insert or update on table "dwh_inv_project" violates foreign key constraint "dwh_inv_project_fk1"

Cause

The foreign key constraint dwh_inv_project_fk1 is a constraint on investment_key of the dwh_inv_project table, that refers to investment_key of dwh_inv_investment table. So, this error message comes up when there is a record in dwh_inv_project whose parent record is not present in dwh_inv_investment table.

Environment

Version: 15.9.0

Resolution

It was noticed that a process was running in loop that was updating an Idea record continuously, thereby changing the last_updated_date on that record in the inv_investments table. Since the clarity_updated_date was greater than the load start date, the relevant record was being left out resulting in a mismatch.

The process had an incorrect logic in the UPDATE trigger that was corrected. This resulted in it not getting triggered unnecessarily and thus not updating the inv_investments row continuously.

Query to identify this issue would be as below. It should return at least one record.

select *
from dwh_inv_project
where investment_key not in (
    select investment_key
    from dwh_inv_investment
)