Re: How to change primary key in a table

2009/11/12 Rikard Bosnjakovic <rikard(dot)bosnjakovic(at)gmail(dot)com>:
> I have the following table:
>
> CREATE TABLE penalty_codes (
>        penalty_code varchar(10),
>        penalty_name varchar(32),
>        penalty_name_sv varchar(40),
>        penalty_id serial PRIMARY KEY
> );
>
> which I have been using for a year or two. Today I realized that the
> id-column being a primary key is really not useful, while the
> code-column is instead. Three other tables refer on the id-values so
> the column can certainly not be dropped, but is it possible to change
> the primary key to the code-column without breaking things?
>
> I tried this:
>
> ========================================
> SQL error:
> ERROR:  cannot drop constraint penalty_codes_pkey on table
> penalty_codes because other objects depend on it
> HINT:  Use DROP ... CASCADE to drop the dependent objects too.
>
> In statement:
> ALTER TABLE "penalty_codes" DROP CONSTRAINT "penalty_codes_pkey"
> ========================================
>
> I'm aware of what CASCADE does when you drop a table for instance, but
> I have no idea what happens if you cascade drop a primary key.
>
> How can I switch the primary keys in this table? Is it possible?
>

You will have to remove foreign keys that point to this primary key
column before dropping it. After doing so, you won't be able to
reapply the foreign keys unless you add a UNIQUE constraint to your
penalty_id column, preferrably also specifying NOT NULL.

Regards

Thom