pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedChanging primary keys
2+ messages / 2 participants
[nested] [flat]
* Changing primary keys
@ 2004-03-10 17:17 David <de4@kent.ac.uk>
2004-03-10 22:06 ` Re: Changing primary keys Jonathan Gardner <jgardner@jonathangardner.net>
0 siblings, 1 reply; 2+ messages in thread
From: David @ 2004-03-10 17:17 UTC (permalink / raw)
To: pgsql-sql
Is it possible to change the primary key of a relation? I want to add an
attribute, that i already have in the realtion, to the primary key (yes i
realise i designed my model pretty badly)
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Changing primary keys
2004-03-10 17:17 Changing primary keys David <de4@kent.ac.uk>
@ 2004-03-10 22:06 ` Jonathan Gardner <jgardner@jonathangardner.net>
0 siblings, 0 replies; 2+ messages in thread
From: Jonathan Gardner @ 2004-03-10 22:06 UTC (permalink / raw)
To: David <de4@kent.ac.uk>; +Cc: pgsql-sql
On Wednesday 10 March 2004 09:17 am, David wrote:
> Is it possible to change the primary key of a relation? I want to add an
> attribute, that i already have in the realtion, to the primary key (yes i
> realise i designed my model pretty badly)
>
It sure is.
First, ensure that the values are indeed not NULL and unique.
Next, alter the table to drop the primary key. (See ALTER TABLE).
Then, alter the table to add the new primary key.
If you have other tables that have a foreign key references to this table,
they may have to change.
I don't need to tell you to think really hard about schema changes and the
impact it will have on the application before you do stuff. And be sure you
are not doing this on the production database without testing it first!
My personal preference would be to add the attribute, and then make a new
primary key column. I really don't like multi-column primary keys as they
are a bit more difficult to use. Even if the boss wants to use the old-pk +
attribute as the pk, you will use the new pk as the actual pk for joins and
such. You may put a not null unique constraint on the old-pk + attribute
combination so that you are guaranteed that the data won't violate the
boss's idea of what the pk should be.
--
Jonathan Gardner
jgardner@jonathangardner.net
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-03-10 22:06 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-03-10 17:17 Changing primary keys David <de4@kent.ac.uk>
2004-03-10 22:06 ` Jonathan Gardner <jgardner@jonathangardner.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox