Re: how to disable all pkey/fkey constraints globally

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

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.

thanks

On 10/5/2011 3:27 AM, Joe Abbate wrote:
> On 10/05/2011 04:49 AM, depstein(at)alliedtesting(dot)com wrote:
>>> -----Original Message-----
>>> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
>>> owner(at)postgresql(dot)org] On Behalf Of J.V.
>>> Sent: Tuesday, October 04, 2011 10:00 PM
>>> To: pgsql-general
>>> Subject: [GENERAL] how to disable all pkey/fkey constraints globally
>>>
>>> Is there a generic way to drop just all primary key and foreign key constraints
>>> on a given table?
>>>
>>> I know how to do given the specific name of the constraint.
>>>
>>> same question but one statement that would just disable all primary key and
>>> foreign key constraints on a given database?
>>>
>>> and am assuming the reverse could not be done because would have to re-
>>> create each one individually?
>>>
>>> Maybe I do not want to drop, so is there a way to simply disable all globally
>>> (not drop)& then enable all globally?
>>>
>> You can find all foreign key constraints for a given table, save
>> constraint definitions, drop constraints, and later re-enable them.
>> Look into table pg_constraint and function pg_get_constraintdef.
> If you'll allow me to toot my horn, here's an alternative:
>
> - Use dbtoyaml [1] to output your tables to a file, say, yaml1
> - Edit the yaml1 file, searching for primary_key and foreign_keys and
> remove those you want to drop, save the result to a different file, say,
> yaml2
> - Use yamltodb [2] with yaml2 to generate SQL to drop the primary keys
> and foreign keys, in the correct dependency order (at least that's what
> it's supposed to do, make sure you use the -1 option), to a file, say, sql1
> - Run sql1 through psql to drop the constraints
> - Use yamltodb with yaml1 to generate SQL to recreate the primary keys
> and foreign keys to, say sql2
> - Run sql2 through psql to recreate the constraints
>
> Regards,
>
>
> Joe
>
> [1] http://www.pyrseas.org/docs/dbtoyaml.html
> [2] http://www.pyrseas.org/docs/yamltodb.html
>

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Henry Drexler 2011-10-20 21:13:04 Re: plpgsql at what point does the knowledge of the query come in?
Previous Message Raymond O'Donnell 2011-10-20 20:57:24 Re: plpgsql at what point does the knowledge of the query come in?