Re: adjusting primary key

Thenx a lot. That does it!!

-R

On Tue, 2006-10-10 at 16:23 +0200, Matthias(dot)Pitzl(at)izb(dot)de wrote:
> You have to create t2 as following:
> CREATE TABLE t2 (id int, grp int references t1(id) ON UPDATE CASCADE ON
> DELETE CASCADE, info text);
>
> Through the cascade commands Postgresql will check the t2 table on rows
> which have to be deleted or updated according to your changes in t1.
>
> For changing the existing table take a look at the ALTER TABLE commands.
>
> Greetings,
> Matthias
>
> > -----Original Message-----
> > From: pgsql-general-owner(at)postgresql(dot)org
> > [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Rafal Pietrak
> > Sent: Tuesday, October 10, 2006 3:59 PM
> > To: pgsql-general(at)postgresql(dot)org
> > Subject: [GENERAL] adjusting primary key
> >
> >
> > Hi All,
> >
> > I have two tables:
> > CREATE TABLE t1 (id int not null unique, info text);
> > CREATE TABLE t2 (id int, grp int references t1(id), info text);
> >
> > Now, at certain point (both tables populated with tousends of records,
> > and continuesly referenced by users), I need to adjust the value of an
> > ID field of table T1.
> >
> > How can I do that? On the life system?
> >
> > Obvious solution like:
> > UPDATE t1 SET id=239840 where id=9489;
> > or in fact:
> > UPDATE t1 SET id=id+10000 where id<1000;
> > wouldn't work, regretably.
> >
> > Naturally I need to have column t2(grp) adjusted accordingly
> > - within a
> > single transaction.
> >
> > Asking this, because currently I've learned, that I can adjust the
> > structure of my database (add/remove columns at will, reneme those,
> > etc.), but I'm really stuck with 'looking so simple' task.
> >
> > Today I dump the database and perl-edit whatever's necesary
> > and restore
> > the database. But that's not a solution for life system.
> >
> > Is there a way to get this done? life/on-line?
> > --
> > -R
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 1: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo(at)postgresql(dot)org
> > so that your
> > message can get through to the mailing list cleanly
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
--
-R