Thread: Suspend Referential Integrity?

I'm trying to load a database ported from Oracle.  The commands I was given to
load data into the table are in alphabetical order; when I run them, I get
errors because I'm violating RI rules (eg. table "CUSTOMER" has RI to "SOURCE",
but CUSTOMER attempts to load before SOURCE).

Certainly, I could try to reorder the load statements, but that would be just a
huge PITA.  Is there a way to temporarily suspend RI on a PostgreSQL
database/schema, and then "fix it later?" Either delete the rows that break
rules or add rows to fix the violations?

TIA

=================================================================
| 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.                           |
================================================================

On Mon, Aug 08, 2005 at 09:49:38 -0400,
  Jim Jarrett <jarrett@rpa.net> wrote:
> I'm trying to load a database ported from Oracle.  The commands I was given to
> load data into the table are in alphabetical order; when I run them, I get
> errors because I'm violating RI rules (eg. table "CUSTOMER" has RI to "SOURCE",
> but CUSTOMER attempts to load before SOURCE).
>
> Certainly, I could try to reorder the load statements, but that would be just a
> huge PITA.  Is there a way to temporarily suspend RI on a PostgreSQL
> database/schema, and then "fix it later?" Either delete the rows that break
> rules or add rows to fix the violations?

For an initial load the best thing is probably to not include the foreign key
constraints in the initial table definitions and add them at the end of the
load using alter table commands.

On 8/15/05 1:31 PM, "Bruno Wolff III" <bruno@wolff.to> wrote:

> On Mon, Aug 08, 2005 at 09:49:38 -0400,
> Jim Jarrett <jarrett@rpa.net> wrote:
>> I'm trying to load a database ported from Oracle.  The commands I was given
>> to
>> load data into the table are in alphabetical order; when I run them, I get
>> errors because I'm violating RI rules (eg. table "CUSTOMER" has RI to
>> "SOURCE",
>> but CUSTOMER attempts to load before SOURCE).
>>
>> Certainly, I could try to reorder the load statements, but that would be just
>> a
>> huge PITA.  Is there a way to temporarily suspend RI on a PostgreSQL
>> database/schema, and then "fix it later?" Either delete the rows that break
>> rules or add rows to fix the violations?
>
> For an initial load the best thing is probably to not include the foreign key
> constraints in the initial table definitions and add them at the end of the
> load using alter table commands.

As an additional point, sometime I find it helps to do an initial load into
a different schema (same database) and then use a sql script/function to do
"cleanup" into the final schema with RI in place.