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@xxxxxxxxxxxxxxxxx wrote:
-----Original Message-----
From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-
owner@xxxxxxxxxxxxxx] On Behalf Of J.V.
Sent: Tuesday, October 04, 2011 10:00 PM
To: pgsql-general
Subject: 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
