Thread: How to ALTER a TABLE to change the primary key?

Hi all.
I'd need to modify the primary key definition in an already populated table.
How can I do it?

Thanks.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 10/26/07 04:39, Reg Me Please wrote:
> Hi all.
> I'd need to modify the primary key definition in an already populated table.
> How can I do it?

Have you tried dropping the constraint, and creating a new one?

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

- --
Ron Johnson, Jr.
Jefferson LA  USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHIbwhS9HxQb37XmcRAjg6AJ4sPW7wPH599JPVdmZ5s25b5yHnHQCeJtsr
0TRv9XcYy2+04FW+1dNIYFc=
=ldEW
-----END PGP SIGNATURE-----

On Oct 26, 2007, at 5:39 , Reg Me Please wrote:

> I'd need to modify the primary key definition in an already
> populated table.
> How can I do it?

Drop the primary key constraint and create a new one. You can do this
inside a transaction.

test=# \d strings
    Table "public.strings"
   Column  | Type | Modifiers
----------+------+-----------
a_string | text | not null
Indexes:
     "strings_pkey" PRIMARY KEY, btree (a_string)

test=# begin; alter table strings drop constraint strings_pkey; alter
table strings add constraint new_pkey primary key (a_string); commit;
BEGIN
ALTER TABLE
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
"new_pkey" for table "strings"
ALTER TABLE
COMMIT
test=# \d strings;
    Table "public.strings"
   Column  | Type | Modifiers
----------+------+-----------
a_string | text | not null
Indexes:
     "new_pkey" PRIMARY KEY, btree (a_string)

Michael Glaesemann
grzm seespotcode net