From: Jonathan Gardner <jgardner@jonathangardner.net>
To: "David" <de4@kent.ac.uk>
Subject: Re: Changing primary keys
Date: Wed, 10 Mar 2004 14:06:48 -0800
User-Agent: KMail/1.6
Cc: <pgsql-sql@postgresql.org>
References: <007101c406c3$807a13d0$42660c81@ad.kent.ac.uk>
In-Reply-To: <007101c406c3$807a13d0$42660c81@ad.kent.ac.uk>
MIME-Version: 1.0
Content-Disposition: inline
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Message-Id: <200403101406.48454.jgardner@jonathangardner.net>
Received-SPF: pass (dervish.jonathangardner.net: sender at 207.171.180.101 was
	authenticated)

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