Constraints Not Enforced

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 :).

4 thoughts on “Constraints Not Enforced”

    1. Hi Viswa,

      Thanks for visiting my blog and posting a question.

      As mentioned, Snowflake only enforce NOT NULL constraints, not others. The ENFORCED keyword in the constraint properties are mainly for portability from other SQL languages.

      Please refer to doc:
      https://docs.snowflake.com/en/sql-reference/constraints-properties.html#properties-for-all-constraints

      >> Note that NOT NULL is the only constraint enforced by Snowflake

      Also please refer to:
      https://docs.snowflake.com/en/user-guide/table-considerations.html#label-table-considerations-referential-integrity-constraints

      Cheers
      Eric

    1. Hi Visu,

      As mentioned in my blog post, it is for portability from other SQL languages. On the same page that you mentioned in your comment, look for below text:

      >> These properties are provided to facilitate migrating from other databases. They are not enforced or maintained by Snowflake. This means that the defaults can be changed for these properties, but changing the defaults results in Snowflake not creating the constraint.

      Please also see:
      https://docs.snowflake.com/en/sql-reference/constraints-properties.html#properties-for-all-constraints

      Cheers
      Eric

Leave a Reply

Your email address will not be published.