Learn Snowflake Constraints Primary Key, Unique Key, Foreign Key and Not Null

By: Sergey Gigoyan   |   Updated: 2022-12-07   |   Comments   |   Related: More > Snowflake

Problem

As constraints are essential in database development, we will devote this article to Snowflake constraints. We will discuss the constraint types supported by Snowflake and will compare them to SQL Server constraints. This article can be helpful specifically for those who have experience with MS SQL constraints and want to understand constraints in Snowflake. It is assumed that the readers have working experience with constraints or at least a basic understanding of ANSI SQL constraints.

Solution

Constraints are aimed at supporting data integrity and consistency in databases. The supported ANSI SQL standard constraint types in Snowflake are the following:

• PRIMARY KEY
• UNIQUE KEY
• FOREIGN KEY
• NOT NULL

It is very important to note that while Snowflake supports the above mentioned constraints, it does not enforce most of them. The only enforced constraint in Snowflake is NOT NULL. For all others, only defining and maintaining are supported. In Snowflake, these constraints are used mainly for data modeling purposes, for providing compatibility with other databases, and for supporting some client tools to work with constraints. In contrast, in SQL Server, all of these constraints are enforced. Moreover, the CHECK constraint is also supported in SQL Server, which is not provided in Snowflake. Snowflake supports constraints on permanent, transient, and temporary tables.

Now, let's do some hands-on exercises to see how Snowflake supports constraints.

First, we will create a test environment by copying and pasting this code into a Snowflake Web UI worksheet:

CREATE OR REPLACE DATABASE TESTDB;
 
CREATE OR REPLACE SCHEMA TESTDB.TESTSCHEMA;
 
CREATE OR REPLACE TABLE TESTDB.TESTSCHEMA.TestTable ( 
    ID INT PRIMARY KEY, 
    Val CHAR(1) NOT NULL UNIQUE
);

We have created a database, schema, and table with PRIMARY KEY, NOT NULL, and UNIQUE constraints:

To see how constraints are not enforced, we will try to insert duplicate values in the primary key column:

--Primary key is not enforced
INSERT INTO TESTDB.TESTSCHEMA.TestTable (ID, Val)
VALUES (1,'A'), (1,'B');
 
SELECT * FROM TESTDB.TESTSCHEMA.TestTable;

As we can see, these values are successfully inserted. Even though the "ID" column is defined as a primary key, it contains duplicate values:

Now, let's try to insert duplicate values into the "Val" column, which is defined as UNIQUE:

--UNIQUE is not enforced
INSERT INTO TESTDB.TESTSCHEMA.TestTable (ID, Val)
VALUES (2,'C'), (3,'C'); 
 
SELECT * FROM TESTDB.TESTSCHEMA.TestTable;

In this test as well, we can see that duplicate values are successfully inserted:

These two INSERT statements will fail in SQL Server. SQL Server allows duplicates neither in PRIMARY KEY nor UNIQUE columns. In the previous examples, we can see how constraints are not enforced in Snowflake.

In the following example, we will see how enforcement works. As mentioned above, the only constraint enforced by Snowflake is NOT NULL. So, let's try to insert NULL values into the "Val" column, which is defined as NOT NULL:

--NOT NULL is enforced
INSERT INTO TESTDB.TESTSCHEMA.TestTable (ID)
VALUES (4);
 
SELECT * FROM TESTDB.TESTSCHEMA.TestTable;

Unlike the previous two queries, this query fails with an error:

Thus, Snowflake prevents the insertion of NULL values into the non-nullable columns.

Conclusion

Although the PRIMARY KEY, UNIQUE, and FOREIGN KEY constraints are provided in Snowflake, they are not enforced. They are mainly used for data modeling purposes, providing compatibility with other database systems, and supporting third-party tools. The NOT NULL constraint, however, is enforced like in SQL Server.

Next Steps

For additional information, please check out the links below:

• Constraints — Snowflake Documentation
• Overview of Constraints — Snowflake Documentation
• Primary and Foreign Key Constraints - SQL Server | Microsoft Learn
• Unique Constraints and Check Constraints - SQL Server | Microsoft Learn
• table_constraint (Transact-SQL) - SQL Server | Microsoft Learn
• SQL Server Referential Integrity without Foreign Keys (mssqltips.com)

About the author

Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips