Thread: 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

> 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.

http://www.postgresql.org/docs/8.1/interactive/sql-altertable.html


You will need to determine the name of your primary key constraint so that you can drop it using
the alter table syntax.  I believe that "$> \d+ house" will do this for you.

Next you can create the new primary key constraint following the sytanx in the link.

Regards,

Richard Broersma Jr.

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