Re: disabling constraints

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