Обсуждение: 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? thanks J.V.
On 10/04/11 10:59 AM, J.V. wrote: > Maybe I do not want to drop, so is there a way to simply disable all > globally (not drop) & then enable all globally? IF there was such a method (I don't think there is), to reenable the constraints would require going through every single row of every single table and verify the constraints are valid prior to enabling them. this would require global locks on each table so they didn't get updated between the time said validation starts and when the constraint is actually enabled to prevent any possibility of inconsistency from creeping in. *OUCH* -- john r pierce N 37, W 122 santa cruz ca mid-left coast
> -----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-enablethem. Look into table pg_constraint and function pg_get_constraintdef. Dmitry Epstein | Developer Allied Testing www.alliedtesting.com We Deliver Quality.
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
Create a temp table that will store all the foreign kez constraints
then create a function that add an entry to this table
then write a plpgsql function that reads the constraints and disable them
write another function to read the constraints from the temporary tables and create them again
have a look on pg_constraints , pg_tables
regards
From: Joe Abbate <jma@freedomcircle.com>
To: pgsql-general@postgresql.org
Sent: Wednesday, October 5, 2011 11:27 AM
Subject: Re: [GENERAL] how to disable all pkey/fkey constraints globally
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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@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 >
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
> -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > owner@postgresql.org] On Behalf Of J.V. > Sent: Friday, October 21, 2011 1:11 AM > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] 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 If you know constraint name and schema, then I don't see how you could possibly make it any simpler than the way I alreadysuggested: > >> Look into table pg_constraint and function pg_get_constraintdef. Of course, since this involves the use of a system catalogue, this is neither portable nor very reliable in the long run.You can also get all the information about foreign key constraints from information_schema tables, but that is more complicated.You'll need to join table_constraints, referential_constraints and key_column_usage, probably more than once.