Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Subject: Truncate and Foreign Key References question
Date: Wed, 12 Apr 2006 19:43:00 -0700
Message-ID: 
 <71E37EF6B7DCC1499CEA0316A2568328024BC2DF@loki.wc.globexplorer.net>
Thread-Topic: Truncate and Foreign Key References question
Thread-Index: AcZeo/q0qAWhK0XORBKLSvTK9NNoyg==
From: "Gregory S. Williamson" <gsw@globexplorer.com>
To: <pgsql-sql-owner@postgresql.org>
ReSent-Date: Thu, 13 Apr 2006 00:44:29 -0300 (ADT)
ReSent-From: "Marc G. Fournier" <scrappy@hub.org>
ReSent-To: pgsql-sql@postgresql.org
ReSent-Subject: Truncate and Foreign Key References question
ReSent-Message-ID: <20060413004429.J1096@ganymede.hub.org>

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=3D# \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=3D# \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=3D# alter table work.client_collect_rates drop constraint "$2";
ALTER TABLE

billing=3D# \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=3D# 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.