Re: Suspend Referential Integrity?

On 8/9/05 9:14 AM, "Jim Jarrett"  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