Thread: adjusting primary key

adjusting primary key

From
Rafal Pietrak
Date:
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

Re: adjusting primary key

From
John Sidney-Woollett
Date:
If you could alter the foreign key constraint such that the update on
t1's primary key cascaded to t2, that would help.

However, I'm not sure that you alter the constraint in postgres once
it's created.

Hopefully someone more knowledgeable will be able to respond.

John

Rafal Pietrak wrote:
> 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?

Re: adjusting primary key

From
Alexander Staubo
Date:
On Oct 10, 2006, at 15:59 , Rafal Pietrak wrote:

> 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?

You can temporary drop the constraint inside the transaction:

   begin;
   alter table t2 drop constraint t2_grp_fkey;
   ...
   (do your work)
   ...
   alter table t2 add constraint t2_grp_fkey foreign key (grp)
references t1 (id);
   commit;

In theory, this is an ideal application for constraint deferral
(where constraint checking is done at transaction commit instead of
immediately; see http://www.postgresql.org/docs/8.1/interactive/sql-
set-constraints.html), but from what I know it's not possible to
change the deferral mode on an existing constraint. Therefore, if the
"adjustment" requires the constraint to exist in order to maintain
data integrity, you could add a new constraint with deferral enabled,
and then drop the old one. So:

   begin;
   alter table t2 add constraint t2_grp_fkey2 foreign key (grp)
references t1 (id) deferrable initially immediate;
   alter table t2 drop constraint t2_grp_fkey;
   set constraints all deferred;
   ...
   (do your work)
   ...
   commit;

   begin;
   alter table t2 drop constraint t2_grp_fkey2;
   alter table t2 add constraint t2_grp_fkey foreign key (grp)
references t1 (id) deferrable initially immediate;
   commit;

Alexander.

Re: adjusting primary key

From
Matthias.Pitzl@izb.de
Date:
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@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Rafal Pietrak
> Sent: Tuesday, October 10, 2006 3:59 PM
> To: pgsql-general@postgresql.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@postgresql.org
> so that your
>        message can get through to the mailing list cleanly
>

Re: adjusting primary key

From
Rafal Pietrak
Date:
Thenx a lot. That does it!!


-R

On Tue, 2006-10-10 at 16:23 +0200, Matthias.Pitzl@izb.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@postgresql.org
> > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Rafal Pietrak
> > Sent: Tuesday, October 10, 2006 3:59 PM
> > To: pgsql-general@postgresql.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@postgresql.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