Thread: Removing Constraints Efficiently

I'm attempting to remove constraints (primary/foreign keys) ahead of a
massive import. Once the data has been imported, I would like to
regenerate the constraints I removed. This process is recommended in
PostgreSQL's documentation and incidentally would make import much
more tenable.

However, we've been unable to ascertain the best method to accomplish
this. Currently, I have a couple of very large SQL scripts that
contain ADD CONSTRAINT... and DROP CONSTRAINT... commands. The trouble
is dropping the constraints; our foreign keys are greatly
interdependent, and so a simple command to "DROP CONSTRAINT
constraint_fkey" fails with "ERROR:  cannot drop constraint
constraint_[p/f]key on table table because other objects depend on it
HINT:  Use DROP ... CASCADE to drop the dependent objects too.".

Sadly, merely adding CASCADE; does not fix our woes; since our DROP
list contains all of our keys, if DROP CONSTRAINT 1 CASCADE; deletes
constraint 5 on its way down, we'll error out once we hit DROP
CONSTRAINT 5; with a "constraint does not exist".

As you can see, we are at an impasse. I must learn the proper way to
delete and recreate my keys; this won't even work on a table-by-table
basis, since we still won't be able to delete interdependent objects
without cascading, and then we've just moved the problem into a script
farther on down the line. What's the best way for my person to remove
these keys and be able to continue this project in peace?

All help is deeply appreciated in hugs and gentle grazes.

Signed
Jeff

Jeff Cook <jeff@deserettechnology.com> writes:
> I'm attempting to remove constraints (primary/foreign keys) ahead of a
> massive import. Once the data has been imported, I would like to
> regenerate the constraints I removed. This process is recommended in
> PostgreSQL's documentation and incidentally would make import much
> more tenable.

> However, we've been unable to ascertain the best method to accomplish
> this. Currently, I have a couple of very large SQL scripts that
> contain ADD CONSTRAINT... and DROP CONSTRAINT... commands. The trouble
> is dropping the constraints; our foreign keys are greatly
> interdependent, and so a simple command to "DROP CONSTRAINT
> constraint_fkey" fails with "ERROR:  cannot drop constraint
> constraint_[p/f]key on table table because other objects depend on it
> HINT:  Use DROP ... CASCADE to drop the dependent objects too.".

Hmmm ... I cannot think of any reason that anything would depend on a
foreign-key constraint.  (A look in the sources suggests that the only
possibilities are the triggers implementing the constraint, but those
should be internal dependencies that would just be silently dropped,
not produce the above message.)

However, FK constraints can and do depend on PK constraints.  So what
I would expect to work for this is to drop all the FK constraints,
then all the PK/unique constraints.  Have you tried that ordering?

> Sadly, merely adding CASCADE; does not fix our woes; since our DROP
> list contains all of our keys, if DROP CONSTRAINT 1 CASCADE; deletes
> constraint 5 on its way down, we'll error out once we hit DROP
> CONSTRAINT 5; with a "constraint does not exist".

There was a fix applied for problems of that sort in 8.3.4 and 8.2.10
... what version are you running?

            regards, tom lane