Skip site navigation (1) Skip section navigation (2)

Re: Changing primary keys

From: Jonathan Gardner <jgardner(at)jonathangardner(dot)net>
To: "David" <de4(at)kent(dot)ac(dot)uk>
Cc: <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Changing primary keys
Date: 2004-03-10 22:06:48
Message-ID: 200403101406.48454.jgardner@jonathangardner.net (view raw or flat)
Thread:
Lists: 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(at)jonathangardner(dot)net

In response to

pgsql-sql by date

Next:From: Jonathan GardnerDate: 2004-03-10 22:09:08
Subject: Re: Break a report in Run Time
Previous:From: Marty ScholesDate: 2004-03-10 20:56:46
Subject: Re: Inserting data in a table using sub-selects]

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group