By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,177 Members | 976 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,177 IT Pros & Developers. It's quick & easy.

disabling constraints

P: n/a
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 todo 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
*

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
>>>>> "DP" == David Parker <dp*****@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: kh***@kciLink.com Rockville, MD +1-301-869-4449 x806
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #2

P: n/a
kh***@kcilink.com (Vivek Khera) writes:
>> "DP" == David Parker <dp*****@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
Nov 23 '05 #3

P: n/a
Vivek Khera <kh***@kcilink.com> writes:
"DP" == David Parker <dp*****@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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.