How to ALTER TABLE in Amazon Redshift to set two columns as Primary Key?

Question

I would like to alter table in Amazon Redshift, and put two columns as a composite Primary key?

I tried this code snippet:

ALTER TABLE tableNameHere ADD CONSTRAINT PK_1 PRIMARY KEY (col1);

But it worked just to set just one single primary key.

Then I tried:

ALTER TABLE tableNameHere ADD CONSTRAINT PK_1 PRIMARY KEY (col1, col2);

And I get an error:

Invalid operation: multiple primary keys for table "t_sim_data" are not allowed;

PS_1: I already read Redshift : defining composite primary key but in that question it is about creating not altering a table.

PS_2: I have other tables in our cluster where I can see that they have a composite (of two columns) primary key. So it is definitely possible to have a composite Primary Key in Amazon Redshift.

PS_3: I know the power of Redshift is in setting DistKey and SortKey, but I need to set a composite primary key for a table for a specific need.

Answer 1

1st drop the PK_1 constraint then create the 2nd

ALTER TABLE tableNameHere DROP CONSTRAINT PK_1;

then

ALTER TABLE tableNameHere ADD CONSTRAINT PK_1 PRIMARY KEY (col1, col2);