Redshift Unique Key Constraint and Syntax

  • Post author:
  • Post last modified:September 19, 2022
  • Post category:Redshift
  • Reading time:3 mins read

Redshift Unique 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 Unique 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.

Read:

Redshift Unique Key Constraint Syntax

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

create table UniqueKey_demo (
 col1 int NOT NULL [UNIQUE]
 ,col2 date
 ,col3 varchar(60 ) 
, UNIQUE (col1)
) distkey(col1)
compound sortkey(col1, col2);

Now, let us test if the Redshift unique key is enforced. Try to insert duplicate values to the column mentioned as UNIQUE.

insert into UniqueKey _demo values (1,'test1'); 
insert into UniqueKey _demo values (1,'test1');

Above insert statements will successfully insert the data into table because UNIQUE constraint is not enforced on the table.

Alter Redshift Table to add Unique Key Constraint Syntax

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

Below is the syntax and example:

ALTER TABLE ForeignKey_demo ADD CONSTRAINT unq_1 UNIQUE (col1);

Related Articles,

Tags: