martedì 21 maggio 2013

ORA-02292: integrity constraint (.) violated - child record

I was writing an ODI procedure from creating a static dimension from scratch. The procedure looked like this:

begin
DELETE FROM <DIM_TABLE>;
INSERT INTO <DIM_TABLE> VALUES (0,1,etc.);

COMMIT;
end


When I run it, I got the following error:

ORA-02292: integrity constraint (<OWNER>.<FK_CONSTRAINT>) violated - child record

The error means that I was trying to delete values that were used in a column that had a foreign key constraint on it.
To overcome this issue, without deleting any value in the referencing table, you first have to disable the constraint and then enable it again once you have finished populating the table.
So the procedure will look like this:


begin
execute immediate 'ALTER TABLE <DIM_TABLE> DISABLE CONSTRAINT <OWNER>.<FK_CONSTRAINT>';
DELETE FROM <DIM_TABLE>;
INSERT INTO <DIM_TABLE> VALUES (0,1,etc.);
COMMIT; 

execute immediate 'ALTER TABLE <DIM_TABLE> ENABLE CONSTRAINT <OWNER>.<FK_CONSTRAINT>';
end

Nessun commento:

Posta un commento