How to use Redshift Unique Key Constraint? Its Syntax

In my other Redshift related article, we have discussed how to define a primary key, foreign key and NOT NULL constraint. In this article, we will check how to use Redshift UNIQUE KEY constraint with its syntax.

Redshift Unique Key Constraint

Amazon AWS Redshift is an analytical columnar database. Many transactional database features are not supported on Redshift. You can define a unique key, but Unique key is informational only; They are not enforced by Amazon Redshift. The 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.

Amazon Redshift allows you to create unique key constraint while create a table in the database, but it will not be enforced while loading data into tables. However, Redshift query planner uses these constraints to create a better query execution plan.

Read:

How to use Redshift Primary Key Constraint?

Redshift Unique Key Constraint Syntax

There are different methods that you can use to add unique key constraint to the table.

Column level UNIQUE KEY – Add unique key constraint columns while creating table.
Alter Table to Add UNIQUE KEY Column – You can also use alter table command to add unique key constraint after table is created.

You can add UNIQUE KEY constraint while creating a table either on column level or on table level.

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

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

insert into Table_UniqueKey_demo values (1,'test','test1' ); 
insert into Table_UniqueKey_demo values (1,'test', 'test1');

These insert statements will successfully insert the data into a table. Hence UNIQUE constraint is not enforced on the Redshift table. Amazon Redshift supports defining and maintaining constraints, but does not enforce them, except for NOT NULL constraints, which are always enforced.

Alter Table to add Unique Key Constraint

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

Unique Key Constraint Syntax

Following is the syntax and example:

ALTER TABLE ForeignKey_demo ADD CONSTRAINT unq_1 UNIQUE (col1);