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.