Thread: Introducing another primary key field?

I need to introduce an extra field in a table that has one field as
primary key. The "problem" is that this new field should be combined
with the existing pk to create a new combined pk for the table, how to I
change the table? Using the Alternate command? How? Or do I need to
create a new table and copy everything to the new table?


Cheers,

BTJ

--
-------------------------------------------------------------------------------
Bjørn T Johansen

bjorn@havleik.com
-------------------------------------------------------------------------------
Someone wrote:
"I understand that if you play a Windows CD backwards you hear strange
Satanic messages"
To which someone replied:
"It's even worse than that; play it forwards and it installs Windows"

On Aug 26, 2004, at 2:05 PM, Bjørn T Johansen wrote:

> I need to introduce an extra field in a table that has one field as
> primary key. The "problem" is that this new field should be combined
> with the existing pk to create a new combined pk for the table, how to
> I change the table?

Inside a transaction, you'll want to drop the old primary key
constraint (ALTER TABLE foo DROP CONSTRAINT ...), add the new column
(ALTER TABLE foo ADD COLUMN newcol ... ), fill it with some kind of
data (UPDATE foo set newcol = ... ), and add the new constraint (ALTER
TABLE foo ADD CONSTRAINT ... )

I think you'll have to set the new column NOT NULL also (ALTER TABLE
foo ALTER COLUMN newcol SET NOT NULL) to be able to make it part of a
key.

Here are some docs that might help you:
<http://www.postgresql.org/docs/current/static/sql-altertable.html>
<http://www.postgresql.org/docs/current/static/ddl-constraints.html>

Hope this helps!

Michael Glaesemann
grzm myrealbox com