Re: how to disable all pkey/fkey constraints globally

From: Joe Abbate <jma(at)freedomcircle(dot)com>
To: "J(dot)V(dot)" <jvsrvcs(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: how to disable all pkey/fkey constraints globally
Date: 2011-10-20 21:41:47
Message-ID: 4EA0959B.5070505@freedomcircle.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On 10/20/2011 05:10 PM, J.V. wrote:
> Is there a simpler way than this to query the database for meta-data and
> get the constraint definitions?
>
> If I have the constraint name (which I do), I could store the constraint
> definition to a file or database table and recreate them if I could get
> the definition.
>
> This seems like a very simple thing to do, but nowhere can I find the
> meta-data I would need to first save the constraint, to later re-create it.

The metadata is all in the PostgreSQL catalogs. One could write
PL/pgSQL (or other PL) functions to select all the info from the
catalogs into a simple text (SQL statement) format (e.g., ALTER TABLE
tbl ADD CONSTRAINT ...), and also to generate or execute the disabling
(ALTER TABLE tbl DROP CONSTRAINT ...) statements.

You may think it's very simple, but I can assure you that adding or
dropping constraints is not so easy because the ADDs and DROPs have to
be issued in an order that takes into account inter-table/constraint
dependencies (including, in the general case, constraints that are
inherited between tables).

Joe

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Raymond O'Donnell 2011-10-20 21:41:52 Re: plpgsql at what point does the knowledge of the query come in?
Previous Message Henry Drexler 2011-10-20 21:31:48 Re: plpgsql at what point does the knowledge of the query come in?