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:
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 :).
what is the use of “enforced” in the constraint properties.
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
what is the use of “enforced” option in snowflake create table statement.
example: create table table2 (
col1 integer not null,
col2 integer not null,
constraint pkey_1 primary key (col1, col2) enforced
);
https://docs.snowflake.com/en/sql-reference/sql/create-table-constraint.html
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