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
