disabling constraints

"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/

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

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

Hi. Thanks for responding.

I don't *think* there are circular references, but the nature of the
problem is that the code doesn't know anything about the semantics of
the schema - it just gets the list of tables, and there are multiple
schemas it needs to handle, all of which may change over time. That's
why I was hoping for some kind of global "disable constraints" command.

I guess it should be possible to generate the proper table order based
on loading up all of the constraints from the catalog. It seems like
more trouble than I want to go to for this problem, but maybe it
wouldn't be too difficult...

- DAP