Re: Changing primary keys

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