Re: how to disable all pkey/fkey constraints globally

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@alliedtesting.com wrote:
>>> -----Original Message-----
>>> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
>>> owner@postgresql.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
>