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
Hi Eric conceptually denormalization does not imply that keys are not needed or that duplicates are allowed. Even tables (more correctly relations) in first normal form (as defined by E. F. Code) must have at least one key defined and enforced. Duplication (as well as normalisation) is not a matter of performance (physical implementation) but logic (you are manifesting a very common confusion between logical system and physical implementation, a JOIN is not fast or slow since it is a logical concept) , this assuming we are talking about the relational data model. What happens with Snowflake is that it clearly is not a relational database (neither are any of the SQL databases available in the market) but a super spreadsheet management system. I would really recommend you read at least some of the books by C. J. Date as a starting point so you can review your understanding of the concepts you mention here. He has an entire book on how you can try to use SQL products relationally, but this is clearly at least very hard, being positive.