471,090 Members | 1,387 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,090 software developers and data experts.

disabling constraints

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
3 9896
>>>>> "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
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
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.

Similar topics

5 posts views Thread by Ghulam Farid | last post: by
reply views Thread by Sharon Cowling | last post: by
reply views Thread by Oleg Lebedev | last post: by
4 posts views Thread by pankaj_wolfhunter | last post: by
3 posts views Thread by santhoshb99 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.