Truncate and Foreign Key Constraint question

From: "Gregory S(dot) Williamson" <gsw(at)globexplorer(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Truncate and Foreign Key Constraint question
Date: 2006-04-13 03:37:57
Message-ID: 71E37EF6B7DCC1499CEA0316A2568328024BC2E0@loki.wc.globexplorer.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

This is in postgres 8.1:
PostgreSQL 8.1.1 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3)

I've got a table in one schema (work.client_collect_rates) which has an FK constraint with a table, content.collections_l (definitions shown below). There's about 500 entries currently in my collections_l table.

I need to wipe out the contents of the collections_l table nightly and refresh it from a remote master source. (Don't ask ... long & sordid history)

As the sequence below shows, I dropped the FK constraint successfully, but when I run TRUNCATE collections_l it says:

ERROR: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "client_collect_rates" references "collections_l" via foreign key constraint "$2".
HINT: Truncate table "client_collect_rates" at the same time.

This truncation of the second table is *not* an option, but since the constraint "$2" is clearly gone, I am wondering what in the name of sweet apples is going on ? Is this a bug ? Have a developed premature senility ?

Any clues for the clueless would be gratefully accepted!

TIA,

Greg Williamson
DBA
GlobeXplorer LLC

billing=# \d work.client_collect_rates
Table "work.client_collect_rates"
Column | Type | Modifiers
---------------+---------+-----------
contract_id | integer | not null
collection_id | integer | not null
rate | numeric |
break_1 | numeric |
rate_1 | numeric |
break_2 | numeric |
rate_2 | numeric |
break_3 | numeric |
rate_3 | numeric |
break_4 | numeric |
rate_4 | numeric |
Indexes:
"clnt_colrate_ndx" UNIQUE, btree (contract_id, collection_id)
Foreign-key constraints:
"$1" FOREIGN KEY (contract_id) REFERENCES client_contracts(contract_id)
"$2" FOREIGN KEY (collection_id) REFERENCES content.collections_l(collect_id)

billing=# \d content.collections_l
Table "content.collections_l"
Column | Type | Modifiers
----------------------+------------------------+--------------------
collect_id | integer | not null
owner | integer |
collection_name | character(50) |
begin_date | date |
end_date | date |
pos_accuracy | integer |
res_accuracy | integer |
loc_code | character(30) |
color | integer | default 0
category_id | integer |
is_mosaic | integer | not null default 0
detail_metadata_view | character varying(255) |
jdbc_url | character varying(255) |
jdbc_driver | character varying(255) |
Indexes:
"collections_l_pkey" PRIMARY KEY, btree (collect_id)
"collect_own_ndx" btree ("owner", collect_id)

billing=# alter table work.client_collect_rates drop constraint "$2";
ALTER TABLE

billing=# \d work.client_collect_rates
Table "work.client_collect_rates"
Column | Type | Modifiers
---------------+---------+-----------
contract_id | integer | not null
collection_id | integer | not null
rate | numeric |
break_1 | numeric |
rate_1 | numeric |
break_2 | numeric |
rate_2 | numeric |
break_3 | numeric |
rate_3 | numeric |
break_4 | numeric |
rate_4 | numeric |
Indexes:
"clnt_colrate_ndx" UNIQUE, btree (contract_id, collection_id)
Foreign-key constraints:
"$1" FOREIGN KEY (contract_id) REFERENCES client_contracts(contract_id)

(Note that the "$2" FK is gone...)

billing=# truncate content.collections_l;
ERROR: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "client_collect_rates" references "collections_l" via foreign key constraint "$2".
HINT: Truncate table "client_collect_rates" at the same time.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2006-04-13 05:02:00 Re: Truncate and Foreign Key Constraint question
Previous Message Joshua D. Drake 2006-04-13 03:10:32 Re: LW Boston