Re: Changing primary keys

First, you'll have to describe the table or fire up pgAdmin to get the
constraint's name.  The default is usually "tablename_pkey".

ALTER TABLE "house" DROP CONSTRAINT "house_pkey";
ALTER TABLE "house" ADD CONSTRAINT "house_pkey" PRIMARY KEY
("house_id");

Beware: PostgreSQL will yell at you if the new key has duplicates and
will not create the constraint.  PostgreSQL will also probably yell at
you if you try to drop a constraint which is the target of a foreign key
constraint in another table.

--
Brandon Aiken
CS/IT Systems Engineer
-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Carol Cheung
Sent: Wednesday, September 27, 2006 3:55 PM
To: pgsql-novice@postgresql.org
Subject: [NOVICE] Changing primary keys

Hi,

The table 'house' was created with the below code (partial)

CREATE TABLE house (
   house_id bigserial unique not null,
   phone bigint not null,
   address varchar(75) not null,
   primary key(phone, address)
);

My question is how can I switch the primary key from (phone, address) to

house_id, now that the table is created and records have been inserted.

Thanks,
C

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match