Re: Suspend Referential Integrity?

On 8/9/05 9:14 AM, "Jim Jarrett" <jarrett(at)rpa(dot)net> wrote:

> I'm trying to port a database from Oracle to PostgreSQL. I used a perl
> script,
> ora2pg to extract the info from Oracle.
>
> The table data was extracted in alphabetical order. When I attempt to load
> it,
> I get referential integrity violations (eg. I attempt to load CUSTOMER, but
> CUSTOMOER depends on the SOURCE table, which hasn't been loaded yet).
>
> Is there a way to temporarily suspend RI checking so I can load the data and
> then fix it later?

Not on a "whole database" level, as far as I know. You could:

1) Make foreign keys deferrable and load related tables inside a
transaction.

2) Drop foreign key constraints until loading is complete and then
reinstitute them (each one must be done individually).

3) Make a separate "loader" script that does the loading into "loader"
tables that have no foreign key constraints and then write a SQL script to
build the database from the loaded tables.

4) Reorder the oracle dump....

I think all of these require a bit of work beyond something like "SET
REFERENTIAL INTEGRITY OFF", but I would love to be corrected....

Sean