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

How to change primary key in a table

From: Rikard Bosnjakovic <rikard(dot)bosnjakovic(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: How to change primary key in a table
Date: 2009-11-12 11:53:25
Message-ID: d9e88eaf0911120353v3f2360aap14067dac6c2304f6@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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?


-- 
- Rikard

Responses

pgsql-novice by date

Next:From: Thom BrownDate: 2009-11-12 12:00:31
Subject: Re: How to change primary key in a table
Previous:From: Kris KewleyDate: 2009-11-11 00:26:29
Subject: Re: Pros / cons for indexing a small table

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