From: Jonathan Gardner <jgardner@jonathangardner.net>
To: David <de4@kent.ac.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: Changing primary keys
Date: Wed, 10 Mar 2004 14:06:48 -0800
Message-ID: <200403101406.48454.jgardner@jonathangardner.net> (raw)
In-Reply-To: <007101c406c3$807a13d0$42660c81@ad.kent.ac.uk>
References: <007101c406c3$807a13d0$42660c81@ad.kent.ac.uk>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jgardner@jonathangardner.net, de4@kent.ac.uk
Subject: Re: Changing primary keys
In-Reply-To: <200403101406.48454.jgardner@jonathangardner.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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