Redshift Primary Key Constraint and Syntax

Post author:Vithal S
Post last modified:February 8, 2023
Post category:Redshift
Reading time:4 mins read

Redshift Primary key constraint is informational only; they are not enforced by Amazon Redshift. Amazon data warehouse appliance supports referential integrity constraints such as Redshift primary key, foreign key, and unique keys as part of SQL-92 standard requirement.

You can create primary key constraint while creating tables in Redshift database but it will not be enforced while loading Redshift tables. Redshift query planner uses these constraints to create better query execution plan.

If Primary key is set at the column level, it must be on a single column. If PRIMARY KEY is set at the table level, one or more columns can make up a composite primary key.

Read:

Redshift Primary Key Constraint Syntax

You can mention the constraint when creating table either on column level or on table level:

create table PrimaryKey_demo (
 col1 int NOT NULL [PRIMARY KEY]
 ,col2 date
 ,col3 varchar(60 ) 
, primary key(col1)
) distkey(col1)
compound sortkey(col1, col2);

Now, let us test if the Primary key is enforced. Redshift Primary key constraint is not enforced. You can insert the duplicate values in Redshift tables.

Insert duplicate value to created table:

insert into PrimaryKey_demo values (1, current_date, 'test1'); --This 1st value 
insert into PrimaryKey_demo values (1, current_date, 'test1'); -- Duplicate value

Above statement will execute perfectly despite of having primary key constraints on table. Redshift does not enforce constraints on tables.

Alter Redshift Table to add Primary Key Constraint Syntax

You can add the Redshift primary key constraint by altering the tables using ALTER TABLE command.

Below is the syntax and example:

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