Snowflake Primary Key Constraint and Syntax

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

For the compatibility with other databases, Snowflake provides the primary key constraint. The primary key constraint is informational only; It is not enforced when you insert the data into a table. Snowflake supports referential integrity constraints such as primary key, foreign key, unique key, and NOT NULL.

Snowflake Primary Key Constraint

You can create the primary key while creating tables on the Snowflake cloud data warehouse. But, primary key will not be enforced when you load the tables. However, constraints provide valuable metadata. The optimizer uses the primary key to create an optimal execution plan.

A table can have multiple unique keys and foreign keys, but only one primary key.

Snowflake Primary Key Constraint Syntax

There are many methods that you can use to add foreign keys on Snowflake table.

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

Column Level Primary Key

For example, consider below DDL for column level primary key

CREATE TABLE pk_demo_table 
  ( 
     id      INT PRIMARY KEY, 
     NAME    VARCHAR(10), 
     address VARCHAR(100) 
  );

Table Level Primary Key

You can also add the foreign key on table level.

For example,

CREATE TABLE pk_demo_table 
  ( 
     id      INT, 
     NAME    VARCHAR(10), 
     address VARCHAR(100),
     PRIMARY KEY (id)
  );

Alter Table to Add Snowflake Primary Key

You can use ALTER TABLE command to add primary key.

For example,

ALTER TABLE pk_demo_table1 ADD PRIMARY KEY (id);

In the previous sections, we have mentioned that Primary key is not enforced. We can test the same by inserting duplicate values into a table.

INSERT Duplicate Records

Following INSERT statement examples adds the duplicate records to the table which has a primary key defined on one of its column.

INSERT INTO pk_demo_table1 values (1,'a','abc'), (2,'b','bcd'), (1,'a','abc');
+-------------------------+
| number of rows inserted |
|-------------------------|
|                       3 |
+-------------------------+

As you can see, rows are inserted without any issues. Hence, the primary key constraint is not enforced.

You should take different approach to identify and remove the duplicate records.