Removing Constraints Efficiently

From: Jeff Cook <jeff(at)deserettechnology(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Removing Constraints Efficiently
Date: 2009-04-16 23:24:56
Message-ID: 76c03c4c0904161624t1d8d704ua076777d61cde01c@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Francisco Figueiredo Jr. 2009-04-16 23:43:20 Re: need information
Previous Message Jonathan Bond-Caron 2009-04-16 22:59:01 Re: Looking for advice on database encryption