Snowflake – Understanding constraints

,

One of the concepts in Snowflake that I find many struggle with is how constraints are handled. If you come from a traditional database platform such as Microsoft SQL Server, Oracle, MySQL or DB2 you are familiar with constraints and Referential Integrity. The terms Foreign Keys, Default Constraints, Primary Keys, Unique Constraints and Check constraints are your bread and butter and I’m sure you have implemented them all over to enforce data quality at the database layer. They give you that warm fuzzy feeling at night that everything is going to be OK and my data integrity is safe.

So for all of you traditional database developers and DBA’s hang on tight because this will take some getting used to.

Before I dive into each constraint type and explain the differences I want to explain one crucial concept and that is there are constraints that Snowflake allows you to create but they are not enforced nor is there any plans in the roadmap at this time to enforce them, the syntax is only there for migration purposes from other systems. Please re-read that statement until you are ready to move on to the next part.

CONSTRAINT WALKTHROUGH

Ok so lets’ take a look at the matrix below to show the state of how the constraints are supported in Snowflake as well as the documentation links, which in some cases can be confusing so I’ll try to make the descriptions as clear as possible

Constraint Type Typical RDBMS Behavior Snowflake Behavior Snowflake Documentation
Default Constraint Specifies a Default Value for a column if none is specified Only supported for NOT NULL functionality. Value specification is not supported Contraint Properties
Foreign Keys Specifies a requirement that a value for a column must exist elsewhere You can create one but it won’t actually do anything. Constraints
Unique Constraint Allows you to specify that a column or group of columns is unique for all rows in a table (allows NULLS). Insert of a duplicate value will cause an error You can create one but it won’t actually do anything. Constraints
Primary Key Allows you to specify that a column or group of columns is unique for all rows in a table (does no allow NULLS). Insert of a duplicate value will cause an error You can create one but it won’t actually do anything. Constraints

So now that you have reviewed the constraints, you will notice a common theme which is other than NOT NULL default constraints snowflake does now support the enforcement of any constraints.

Why do they support the creation of constraints that do not do anything? Well, there are two reasons.

    Backwards compatibility

One of the reasons constraint creation is supported is for backward compatibility reasons when migrating existing data warehouses to Snowflake in a lift and shift model. This way you can script out your existing database and create it quickly on snowflake without dealing with the hassle of removing all the constraints.

    Third Party Tools

Some third-party tools utilize constraints such as Foreign and Primary Keys to do query optimization and automatic creation of data models such as Power BI and Tableau. By having the support to create constraints and having them part of the definition of the tables, it allows these tools to function and optimize as designed.

I hope this helps clear up constraints and avoids a lot of painful heartache of finding out the hard way that the constraints you created don’t do anything.

Original post (opens in new tab)
View comments in original post (opens in new tab)

Rate

Share

Share

Rate