CODEWITHSUNDEEP
importoracle10g
zono
zono

Reputation: 8584

ORACLE 10g : How to import without foreign key constraint error?

I have a data dump then I wanna import it to another DB (Oracle 10g). The destination DB has already tables (no data) and some foreign key constraints. I will import the data by tables in order to avoid constraint errors. If does anyone know any easier ways, please teach me ?. (Oracle's import tool does not have functions to recognize relations between tables automatically, does it ?)

Upvotes: 1

Views: 8360

Answers (2)

A.B.Cade
A.B.Cade

Reputation: 16915

You can disable all foreign keys first: 

begin
  for cnst in (SELECT constraint_name, table_name FROM user_constraints WHERE constraint_type ='R') loop
    execute immediate 'alter table '|| cnst.table_name||' disable constraint ' || cnst.constraint_name ;
  end loop;
end;

After loading your data do the same to enable them back (just change the alter table command to enable instead of disable

But this is risky, because if the data won't meet your contraints - you'll have a problem ...

Upvotes: 7

Justin Cave
Justin Cave

Reputation: 231821

Assuming that you have a schema-level export, the source schema had those same foreign key constraints, and all the foreign key constraints are between tables in the same schema, the import utility should automatically take care of the foreign key constraints. You shouldn't need to do anything for that (though, of course, you'll have to ignore errors when you do the import because it will try to create the tables and get an error that they already exist).

Upvotes: 1

Related Questions