Re: Change primary key in Postgres 7.3?

Michael Fuhr wrote:
> On Tue, Oct 12, 2004 at 06:10:12PM -0700, Michael Hannon wrote:
>
>>We expect that we WILL eventually rebuild the database, but right now
>>we're looking for a quick fix. Our current programmer tells me that he
>>can't find a way to simply change the primary key "in place" in Postgres.
>
> Does ALTER TABLE not work?
>
> http://www.postgresql.org/docs/7.3/static/sql-altertable.html
>
> Whatever you decide to do, hopefully you have a development system
> on which to test your changes.

I'm not sure what the original poster is asking precisely, but if they
have declared all foreign keys referencing the primary table's primary
key with ON UPDATE CASCADE, then all they need to do is update the
primary table's primary key.

[test(at)lexus] create table foo (key integer not null primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"foo_pkey" for table "foo"
CREATE TABLE
[test(at)lexus] create table bar(other integer not null primary key,
foo_key integer not null references foo(key) on delete cascade on update
cascade);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"bar_pkey" for table "bar"
CREATE TABLE
[test(at)lexus] insert into foo values (1);
INSERT 2433708 1
[test(at)lexus] insert into bar values (100, 1);
INSERT 2433709 1
[test(at)lexus] update foo set key = 2;
UPDATE 1
[test(at)lexus] select * from bar;
other | foo_key
-------+---------
100 | 2
(1 row)

Is that what the original poster is trying to achieve?

Mike Mascari