CODEWITHSUNDEEP
postgresql
David Simic
David Simic

Reputation: 2101

Postgresql add existing column to composite primary key

I have a table in postgresql with a composite primary key. The primary key consists of two columns named:

DATETIME, UID

I have a another (non-null) column named ACTION already existing in this table. How do I add ACTION to the composite primary key? Ie: I'd like the resulting primary key of the table to be the triplet:

DATETIME, UID, ACTION

Upvotes: 25

Views: 36024

Answers (1)

Garrett
Garrett

Reputation: 4394

First drop the primary key constraint. You can get the name of the constraint by typing

\d my_table

and look under indexes for something like:

"my_table_pkey" PRIMARY KEY, btree (datetime, uid)

Drop it by doing:

alter table my_table drop constraint my_table_pkey;

Then create the new composite primary key by doing:

alter table my_table add constraint my_table_pkey primary key (datetime, uid, action);

Upvotes: 58

Related Questions