Re: Changing the Primary Key Column

On 02/23/2011 03:34 PM, Rich Shepard wrote:
> This, too, is new to me. The first table column is a contrived key that is
> no longer needed as a unique row identifier. The second column should
> replace the first as the primary key. The ALTER TABLE command can add a
> primary key to a column, but I don't see how to remove that constraint from
> column1, add it to column2, so I can drop column1.
>

Maybe someone else has a better answer but in my experience you have to
drop the table to change the PK. You can add/drop other indexes or FK.
How about leaving the synthetic PK and adding an extra index for your
other key. Doing that also gets you away from any duplicate primary key
errors. Although Josh Berkus will probably slap me silly for ever
suggesting a synthetic key.