Thread

1. disabling constraints

   David Parker <dparker@tazznetworks.com> — 2004-10-28T14:38:07Z

   I would like to be able to truncate all of the tables in a schema without worrying about FK constraints. I tried issuing a "SET CONSTRAINTS ALL DEFERRED" before truncating, but I still get constraint errors. Is there a way to do something like:
   
   1) disable all constraints
   2) truncate all tables
   3) re-enable all constraints
   
   ?
   
   In the slony project there is a procedure "altertableforreplication" that appears to do 1), but since it is updating pg_catalog tables directly, I don't want to cut/paste before I understand what it's doing!
   
   Is there any "standard" way of doing this?
   
   - DAP
   ----------------------------------------------------------------------------------
   David Parker    Tazz Networks    (401) 709-5130
    
   
   

2. Re: disabling constraints

   Vivek Khera <khera@kcilink.com> — 2004-11-04T20:05:13Z

   >>>>> "DP" == David Parker <dparker@tazznetworks.com> writes:
   
   DP> I would like to be able to truncate all of the tables in a schema
   DP> without worrying about FK constraints. I tried issuing a "SET
   DP> CONSTRAINTS ALL DEFERRED" before truncating, but I still get
   DP> constraint errors. Is there a way to do something like:
   
   Why don't you truncate your tables in an order that won't violate
   FK's?  Or do you have circular references?
   
   -- 
   =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
   Vivek Khera, Ph.D.                Khera Communications, Inc.
   Internet: khera@kciLink.com       Rockville, MD  +1-301-869-4449 x806
   AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/
   
   

3. Re: disabling constraints

   Edmund <ebacon-xlii@onesystem.com> — 2004-11-04T20:34:33Z

   khera@kcilink.com (Vivek Khera) writes:
   
   > >>>>> "DP" == David Parker <dparker@tazznetworks.com> writes:
   > 
   > DP> I would like to be able to truncate all of the tables in a schema
   > DP> without worrying about FK constraints. I tried issuing a "SET
   > DP> CONSTRAINTS ALL DEFERRED" before truncating, but I still get
   > DP> constraint errors. Is there a way to do something like:
   > 
   > Why don't you truncate your tables in an order that won't violate
   > FK's?  Or do you have circular references?
   > 
   > -- 
   
   Because that doesn't work:
   
   test=# create table able(id serial primary key, data text);
   NOTICE:  CREATE TABLE will create implicit sequence "able_id_seq" for "serial" column "able.id"
   NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "able_pkey" for table "able"
   CREATE TABLE
   test=# create table baker(id int references able(id) deferrable, data text);
   CREATE TABLE
   test=# truncate able;
   ERROR:  cannot truncate a table referenced in a foreign key constraint
   DETAIL:  Table "baker" references "able" via foreign key constraint "$1".
   test=# begin;
   BEGIN
   test=# set constraints all deferred;
   SET CONSTRAINTS
   test=# truncate able;
   ERROR:  cannot truncate a table referenced in a foreign key constraint
   DETAIL:  Table "baker" references "able" via foreign key constraint "$1".
   test=# rollback;
   ROLLBACK
   test=#
   
   -- 
   Remove -42 for email
   
   

4. Re: disabling constraints

   Tom Lane <tgl@sss.pgh.pa.us> — 2004-11-04T20:36:32Z

   Vivek Khera <khera@kcilink.com> writes:
   > "DP" == David Parker <dparker@tazznetworks.com> writes:
   > DP> I would like to be able to truncate all of the tables in a schema
   > DP> without worrying about FK constraints. I tried issuing a "SET
   > DP> CONSTRAINTS ALL DEFERRED" before truncating, but I still get
   > DP> constraint errors. Is there a way to do something like:
   
   > Why don't you truncate your tables in an order that won't violate
   > FK's?  Or do you have circular references?
   
   The TRUNCATE command just says "no" if it sees any FKs pointing at the
   target table; order doesn't matter.
   
   It might be an interesting future extension to allow truncating multiple
   tables in a single command, whereupon we could ignore FKs linking two
   such tables.
   
   			regards, tom lane