Thread: Suspend Referential Integrity?

Suspend Referential Integrity?

From
Jim Jarrett
Date:
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?

=================================================================
| Jim Jarrett,Madison, WI       94 Passat GLX                   |
| mailto:jarrett@rpa.net         81 Rabbit Convertible 16v      |
|                                                               |
|    Any problem can be solved with the proper application of   |
|   Force, Heat, Chemicals, or Money.                           |
================================================================

Re: Suspend Referential Integrity?

From
Michael Fuhr
Date:
On Tue, Aug 09, 2005 at 09:14:50AM -0400, Jim Jarrett wrote:
> Is there a way to temporarily suspend RI checking so I can load the data and
> then fix it later?

You could drop and then re-create the foreign key constraints with
ALTER TABLE, or you could create the constraints as DEFERRABLE and
defer them during the load with SET CONSTRAINTS (or make them
INITIALLY DEFERRED).

http://www.postgresql.org/docs/8.0/static/sql-altertable.html
http://www.postgresql.org/docs/8.0/static/sql-createtable.html
http://www.postgresql.org/docs/8.0/static/sql-set-constraints.html

--
Michael Fuhr

Re: Suspend Referential Integrity?

From
Sean Davis
Date:
On 8/9/05 9:14 AM, "Jim Jarrett" <jarrett@rpa.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