Thread: Change primary key in Postgres 7.3?
Greetings. We're running Postgres 7.3 on an Intel linux box (Redhat
Enterprise server, version 3.0). We find ourselves in an awkward
position: we have a database of attributes relating to students that
uses as its primary key the ID number of the student. This is awkward
for the following reasons.
Our university used to use social-security numbers for student ID's.
They stopped doing that a few years ago, but didn't force the change on
existing students. Recently they've made a forced, retroactive change
such that ALL students, past and present, now have a student ID that's
not related to social-security number.
I think this a well-justified change, but, unfortunately for us, it
makes many of the primary keys in our database invalid. This problem is
compounded by the fact that the programmer that set up our Postgres
databases has moved on to another job.
Our current programmer would like to start from scratch, redefine the
schema, rebuild the database, etc. Unfortunately, there are a number of
high-profile applications that depend on the database, and many of them
would surely get broken by this kind of transition.
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.
Is there a way to do this?
Thanks.
- Mike
--
Michael Hannon mailto:hannon@physics.ucdavis.edu
Dept. of Physics 530.752.4966
University of California 530.752.4717 FAX
Davis, CA 95616-8677
DROP CONSTRAINT should be able to drop your pkey and as long as your data supports your new key... you should be set Gavin Michael Hannon wrote: > Greetings. We're running Postgres 7.3 on an Intel linux box (Redhat > Enterprise server, version 3.0). We find ourselves in an awkward > position: we have a database of attributes relating to students that > uses as its primary key the ID number of the student. This is awkward > for the following reasons. > > Our university used to use social-security numbers for student ID's. > They stopped doing that a few years ago, but didn't force the change > on existing students. Recently they've made a forced, retroactive > change such that ALL students, past and present, now have a student ID > that's not related to social-security number. > > I think this a well-justified change, but, unfortunately for us, it > makes many of the primary keys in our database invalid. This problem > is compounded by the fact that the programmer that set up our Postgres > databases has moved on to another job. > > Our current programmer would like to start from scratch, redefine the > schema, rebuild the database, etc. Unfortunately, there are a number > of high-profile applications that depend on the database, and many of > them would surely get broken by this kind of transition. > > 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. > > Is there a way to do this? > > Thanks. > > - Mike
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. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
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@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@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@lexus] insert into foo values (1); INSERT 2433708 1 [test@lexus] insert into bar values (100, 1); INSERT 2433709 1 [test@lexus] update foo set key = 2; UPDATE 1 [test@lexus] select * from bar; other | foo_key -------+--------- 100 | 2 (1 row) Is that what the original poster is trying to achieve? Mike Mascari
Michael Hannon wrote: > Greetings. We're running Postgres 7.3 on an Intel linux box (Redhat > Enterprise server, version 3.0). We find ourselves in an awkward > position: we have a database of attributes relating to students that > uses as its primary key the ID number of the student. This is awkward > for the following reasons. > > Our university used to use social-security numbers for student ID's. > They stopped doing that a few years ago, but didn't force the change on > existing students. Recently they've made a forced, retroactive change > such that ALL students, past and present, now have a student ID that's > not related to social-security number. > > I think this a well-justified change, but, unfortunately for us, it > makes many of the primary keys in our database invalid. This problem is > compounded by the fact that the programmer that set up our Postgres > databases has moved on to another job. > > Our current programmer would like to start from scratch, redefine the > schema, rebuild the database, etc. Unfortunately, there are a number of > high-profile applications that depend on the database, and many of them > would surely get broken by this kind of transition. > > 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. > > Is there a way to do this? > > Thanks. > > - Mike It can be done (see other posts), but I suspect that your programmer has a good reason to be reluctant. <unwanted_advice> Let your programmer start from scratch on a new server. Once he has it set up how he wants it, and has tested it against the other applications, make the switch. If it doesn't work right away, you can always switch back. </unwanted_advice> John