Snowflake Unique Key Constraint and Syntax

Post author:Vithal S
Post last modified:December 20, 2019
Post category:Snowflake
Reading time:5 mins read

Similar to Snowflake primary key and foreign key, unique key constraint is informational only. It is not enforced when you insert rows to the table. The unique key metadata information is used by the Snowflake optimizer to come up with the optimal execution plan.

Snowflake Unique Key Constraint

You can create Unique key constraint while creating tables in the Snowflake cloud database but it will not be enforced while loading tables. The Snowflake query planner uses these constraints to create a better query execution plan.

Similar to the foreign key, a table can have multiple unique key defined on it.

Snowflake Unique Key Constraint Syntax

There are three methods that you can use to add unique keys on Snowflake table.

Column level Unique Key – Add unique key constraint during table creation.
Table Level Unique Key – You can define unique key at the end of column definition after all columns are defined.
Alter Table to Add Unique Key – User Alter table command to add unique key constraint.

Column Level Unique Key

You can mention if the particular column is unique on column level when you create tables.

For example, consider below DDL for column level unique key.

CREATE TABLE uk_demo_table 
  ( 
     id      INT UNIQUE, 
     NAME    VARCHAR(10), 
     address VARCHAR(100)
  );

Table Level Primary Key

You can also add the unique key on table level.

For example,

CREATE TABLE uk_demo_table 
  ( 
     id      INT, 
     NAME    VARCHAR(10), 
     address VARCHAR(100),
	 UNIQUE(id)
  );

Alter Table to Add Snowflake Unique Key

You can use ALTER TABLE command to add unique key.

For example,

ALTER TABLE uk_demo_table ADD UNIQUE (id);

Test Unique Constraint on Snowflake

As mentioned earlier, Unique key will not be enforced when you insert records. You can insert duplicate records.

For example,

INSERT INTO uk_demo_table values (1,'a','abc'), (1,'a','abc');

+-------------------------+
| number of rows inserted |
|-------------------------|
|                       2 |
+-------------------------+

From the above example, you can see duplicated records are inserted. In conclusion, a unique key is no enforced.