3

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.

ZelelB
  • 1,836
  • 7
  • 45
  • 71
  • You will need to `DROP CONSTRAINT` then `COMMIT`before you `ADD CONSTRAINT` again. – MKa Mar 12 '20 at 02:13

1 Answers1

3

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);
Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63
  • Thanks! But still getting the same error: Invalid operation: multiple primary keys for table tableNameHere are not allowed; – ZelelB Mar 29 '19 at 11:01