On Jun 2, 2011, at 6:21 PM, Artiom Makarov wrote: > 2011/6/2 Alexey Klyukin <al...@commandprompt.com>: > >> What would you expect to happen for TRUNCATE .. CASCADE? >> >> One thing I find potentially surprising is that TRUNCATE CASCADE doesn't >> follow the semantics of 'ON DELETE' clause for the foreign key, i.e. it >> would truncate the dependent table even with ON DELETE RESTRICT foreign key. >> Do we need a similar 'ON TRUNCATE' FK clause? >> > > Yes, cascade truncating taked place without ON DELETE RESTRICT > checking. No matter. > Either TRUNCATE must show message with full objects tree - correct > behavior like DROP, or TRUNCATE CASCADE should not delete anything > (strict constraint checking).
Well, in your example it actually shows all the direct dependencies: > create table tr_test1(id1 int, primary key(id1)); > create table tr_test2(id2 int, id int, primary key(id2), foreign key (id) > references tr_test1(id1) MATCH SIMPLE ON UPDATE RESTRICT ON DELETE > RESTRICT); > create table tr_test3(id3 int, id int, primary key(id3), foreign key (id) > references tr_test2(id2) MATCH SIMPLE ON UPDATE RESTRICT ON DELETE > RESTRICT); tr_test3 here depends on tr_test2, and not directly on tr_test1. Still, even if tr_test3.id would reference tr_test.id in your example, only the first dependency is shown (for truncate, delete and probably update): Table "public.test" Column | Type | Modifiers --------+---------+----------- id | integer | not null name | text | Indexes: "test_pkey" PRIMARY KEY, btree (id) Referenced by: TABLE "test2" CONSTRAINT "test2_id_fkey" FOREIGN KEY (id) REFERENCES test(id) ON DELETE RESTRICT TABLE "test3" CONSTRAINT "test3_id_fkey" FOREIGN KEY (id) REFERENCES test(id) ON DELETE RESTRICT postgres=# delete from test; ERROR: update or delete on table "test" violates foreign key constraint "test2_id_fkey" on table "test2" DETAIL: Key (id)=(1) is still referenced from table "test2". I wonder whether this behavior is intentional, to avoid bloat in the logs. To view all the dependencies you can just do \d tablename after receiving the error. -- Alexey Klyukin The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs