Snowflake allows UNIQUE, PRIMARY KEY, FOREIGN KEY and NOT NULL constraints but I read that it enforces only NOT NULL constraint. Then what is the purpose of other keys and under what circumstances do we have to define them? I appreciate any examples.
Thank you, Prashanth.
They express intent, helping people understand your data models. Data modeling tools can use them to generate diagrams. You can also programmatically access them to validate data integrity yourself.
Snowflake supports defining and maintaining constraints, but does not enforce them, except for NOT NULL constraints, which are always enforced.
Constraints are provided primarily for data modeling purposes and compatibility with other databases, as well as to support client tools that utilize constraints. For example, Tableau supports using constraints to perform join culling (join elimination), which can improve the performance of generated queries and cube refresh.
Constraints could also improve the query performance:
Extended Constraint Properties
RELY | NORELY
Specifies whether a constraint in NOVALIDATE mode is taken into account during query rewrite.
By default, this constraint property is set to NORELY.
If you have ensured that the data in the table does comply with the constraints, you can change this to RELY to indicate that the query optimizer should expect the data in the table to adhere to the constraints. Setting this can improve query performance (e.g. by eliminating unnecessary joins).
Understanding How Snowflake Can Eliminate Redundant Joins
In some cases, a join on a key column can refer to tables that are not needed for the join. If your tables have key columns and you are using and enforcing the UNIQUE, PRIMARY KEY, and FOREIGN KEY constraints, Snowflake can improve query performance by eliminating unnecessary joins on key columns.
- Eliminating an Unnecessary Left Outer Join
- Eliminating an Unnecessary Self-Join
- Eliminating an Unnecessary Join on a Primary Key and Foreign Key
