Re: adjusting primary key

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.