25 November 2020

Constraints Not Enforced

By Eric Lin

If you come from RDBMS, we all know that it is important to have constraints in your database, so that your data integrity will be maintained, like Primary Key, Foreign Key, Unique Indexing etc. However, in Snowflake world, it is very different.

Though Snowflake Supports constraints, like the ones I mentioned above, they are NOT enforced. Please refer to Snowflake’s official doc regarding this topic:

Overview of Constraints

The reason being that OLAP database is not designed to work in the same way as traditional OLTP, because it is simply OK to have lots of duplicated data in one table (database denormalization). This helps to perform data reading, as we do not have to perform lots of JOINs from multiple tables.

Snowflake supports those constraints mainly for the purpose of being compatible with other traditional DB vendors, so that users can easily migrate tables from them into Snowflake, without the need to modify exported SQL.

As mentioned from the doc I shared above, the only constraint that is enforced by Snowflake is NOT NULL, and others won’t be checked.

Snowflake supports defining and maintaining constraints, but does not enforce them, except for NOT NULL constraints, which are always enforced.

So don’t be surprised when you see multiple duplicate values for your Primary Key column in Snowflake’s table :).