10

I have a table for which I want to define a composite primary key with two columns in redshift. I am having some trouble with create table syntax. Here is what I am trying to do :

Create table metrics  (
     id varchar(30),
     runtime timestamp,
     category varchar(30),
     location varchar(30))
primary key(id, runtime),
sortkey(runtime);

It is failing with a message : 

ERROR:  syntax error at or near "PRIMARY"

Can anyone please help me figure out how to fix it? Thanks in advance.

Santanu C
  • 1,362
  • 3
  • 20
  • 38

1 Answers1

23

The primary key constraint goes inside the parentheses containing the columns:

Create table metrics  (
     id varchar(30),
     runtime timestamp,
     category varchar(30),
     location varchar(30),
     primary key(id, runtime)
)
sortkey(runtime);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I tried like you mentioned. Create table worked fine. But I am receiving the following error when I try to load data into the table : Multiple primary keys are not allowed. – Santanu C May 09 '14 at 23:59
  • According to the documentation (http://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_NEW.html), *composite* primary keys are allowed and the answer has the right syntax. You cannot have multiple primary keys on a table, and neither your question nor my answer has multiple primary keys. – Gordon Linoff May 10 '14 at 00:02