473,322 Members | 1,287 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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:ha****@physics.ucdavis.edu
Dept. of Physics 530.752.4966
University of California 530.752.4717 FAX
Davis, CA 95616-8677
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #1
4 7008
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


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #2
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/s...ltertable.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/

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #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/s...ltertable.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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #4
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
Nov 23 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Nick | last post by:
Is there a way to reference the 'primary key' field of a record you are actually inserting at the moment. Im using an 'int auto increment' field as the primary key. I could reference it with a...
6
by: J Smith | last post by:
After doing some googling through the lists and such, I wasn't able to arrive at a solution for a problem I've run into recently. I've seen mention of similar symptoms, but my case seems different....
14
by: Craig O'Shannessy | last post by:
Hi all, Just thought I'd mention that I really think this problem needs to be fixed. I I'm patching the 7.4RC1 JDBC drivers as we speak due to this optimiser bug, and it's the third time...
2
by: Rick Gigger | last post by:
I am creating a script to dump a postgres database into a sqlite database. I need to know how to find out programattically what fields are in the primary key of a given table. Is this possible in...
3
by: warwick.poole | last post by:
I am interested in finding out about Enterprise scale Postgres installations and clustering, especially on Linux. Essentially I would like to know the possibility that Postgres can store the...
2
by: Brendan Jurd | last post by:
Hello all, I have a particular application sitting on postgres that requires a complete log of all inserts, updates and deletes, along with the application user who made the change. There is a...
18
by: Joe Lester | last post by:
This thread was renamed. It used to be: "shared_buffers Question". The old thread kind of died out. I'm hoping to get some more direction by rephrasing the problem, along with some extra...
5
by: Thomas F.O'Connell | last post by:
We've got a table that has a definition as follows: CREATE TABLE linking_table ( fk int8 REFERENCES source_table( pk1 ), value int8, PRIMARY KEY( fk1, value ) ); I would've thought that...
1
by: kcsriram80 | last post by:
Postgres voilates Primary key .I would be able to find duplicates on database. Also it voilates the OID.I dont know the reason .it happend only once on DB. Please suggest me is there a soultion to...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.