Load Data Warehouse job fails on dwh_inv_project_fk1 constraint


Article ID: 208549


Updated On:


Clarity PPM On Premise Clarity PPM SaaS


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"


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.


Version: 15.9.0


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