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