Significance of Constraints in Snowflake

Question

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.

Bradeord

Answer 1

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.

Dolerite

Thank you @waldente. I'm wondering why Snowflake documentation displays syntax that includes key words like: [ NOT ] ENFORCED [ NOT ] DEFERRABLE INITIALLY { DEFERRED | IMMEDIATE } ENABLE | DISABLE VALIDATE | NOVALIDATE. I'm a bit lost, what is the significance of these then? RELY | NORELY – Bradeord

docs.snowflake.net/manuals/sql-reference/sql/… – Bradeord

Another reason is that it makes it easy to migrate DDL from systems with those features. – Dolerite

Thank you @waldente. The migration process from other DB to Snowflake and vice versa doesn't need keywords like NOT ENFORCED/ENFORCED etc. – Bradeord

would say they are there for tools that insist of having them, so those tools run against snowflake. Otherwise support is dead in the water... – Ossian

@Simeon Pilgrim I completely understand your/waldente point and agree with you. At the same time, I'm trying to understand if I missed anything. As per Snowflake documentation (link above), properties like ENFORCED/DEFERRABLE/ENABLE/VALIDATE etc are applicable to all constraints including PRIMARY, FOREIGN, UNIQUE keys besides NOT NULL. So what does it mean when you create a table with PRIMARY KEY ENFORCED/ENABLE etc? – Bradeord

waldente's are 100% accurate. In addition to them, these constraints might be enforceable at a later date as a feature upgrade to the product, so it is important to reserve these keywords now and not have their clients get tripped up by their usage later. – Flutterboard

Thank you @Chris, now it makes more sense to me after reading your comment also in addition to Simeon and waldente. Reserving the keywords ENFORCED etc for future upgrade. I was ok defining PK, UK etc for compatibility purposes but then why do Snowflake has additional keywords ENFORCED etc(for possible feature upgrade in the future). – Bradeord

Answer 2

Constraints

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

Chondriosome