Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id DBF88475D00
	for <pgsql-sql@postgresql.org>; Sat,  4 Jan 2003 10:16:22 -0500 (EST)
Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178])
	by postgresql.org (Postfix) with ESMTP id 34D75475CE7
	for <pgsql-sql@postgresql.org>; Sat,  4 Jan 2003 10:16:21 -0500 (EST)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id 2F442D60C; Sat,  4 Jan 2003 07:16:25 -0800 (PST)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id 20E695C02; Sat,  4 Jan 2003 07:16:25 -0800 (PST)
Date: Sat, 4 Jan 2003 07:16:25 -0800 (PST)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Evgen Potemkin <evgent@ns.terminal.ru>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: Reference integrity question
In-Reply-To: <Pine.LNX.4.33.0301041750560.19906-100000@ns.terminal.ru>
Message-ID: <20030104071505.Y20289-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200301/27
X-Sequence-Number: 10892

On Sat, 4 Jan 2003, Evgen Potemkin wrote:

> > > I have two tables
> > >   news(newsid int4, newscltid int4 references clt(cltid) match full,newstext text)
> > > and
> > >   clt(cltid int4, somedata text).
> > > after clt is renamed , for ex. to clt_old, newscltid start to reference to clt_old.cltid.
> > >   i'm create new table clt(cltid int4, anotherdata text);
> > >
> > > is there any way to make newscltid reference to newly created clt, beside
> > > recreation of news ?
> >
> > If you mean that all newscltids should reference the new table (presumably
> > already loaded)...
> >
> > Since you haven't given a version, let's guess 7.3, in which case the
> > constraint should probably be named "$1" and you can do an
> > ALTER TABLE news drop constraint "$1";
> > ALTER TABLE news add foreign key (newscltid) references clt(cltid) match
> > full;
> >
> v 7.3, but i'm upgraded from 7.2
> \d news
> shows that there is no constraints, but constraint triggers a la
> RI_ConstraintTrigger_262762.
> when i'm dumps schema, first of trigger func parameters is '<unnamed>', so i
> guess it's a contraint name.

In that case you'll need to drop the triggers manually (there are three
triggers, one on the fk table and two on the pw table) and add the
constraint using ALTER TABLE.  For info on the triggers, there's a couple
of documents on techdocs.