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