ORA-02291: parent key not found when inserting multiple rows

Question

I having a problem executing an stored procedure that does multiple inserts

I am a copying 30 tables from a instance of a server to another by a DBLINK:

INSERT INTO table@dblink (column1)
         SELECT column1
         FROM table;

But it results in:

ORA-02291: integrity constraint (string.string) violated - parent key not found

There is only one commit at the end of the procedure.

The 4th table that I'm inserting, has an FK to the first one, and its no recognizing the inserts of the first one (I have tried with deferred constraints and same problem: ORA-02291).

Answer 1

The problem here is you are modifying data (DML) through db-link. This might be ill-managed by Oracle, and cause unexpected behavior. You should do it the other way around: instead of pushing data, drag data through this db-link, and do the inserts locally. Of course, you probably cannot technically do what you want on the destination database...

The solution you have is to deactivate the FK before your inserts, then activate the FK.

However, I am not sure this DDL is possible directly through db-link... You may need to create a procedure to deactivate the FKs on the destination database, and call it via db-link.