Snowflake Primary Keys and Constraints: Declared vs Enforced

1.Defining primary keys and the other key types

Snowflake supports five constraint types from the ANSI SQL standard. The primary key matters most for data integrity because it identifies each row. A PRIMARY KEY means two things at once: no two rows share the value, and the value is never NULL. A UNIQUE constraint keeps only the first rule, so a UNIQUE column can still contain NULLs. NOT NULL keeps only the second rule. A FOREIGN KEY links a column to a key in another table, or in the same table. A CHECK constraint applies a SQL expression to the values a row is allowed to hold.

There is also a limit on how many of each a table can have. A table can declare several unique keys and several foreign keys, but only one primary key. If a row needs more than one column to identify it, you don't add a second primary key. You define one composite primary key across several columns. The columns in a composite key are ordered, and each column has its own key sequence. That order comes back on the next page, when a foreign key has to reference the key.

Sources1

2.Inline and out-of-line constraint syntax

You can declare a constraint when you create the table with CREATE TABLE, or add it later with ALTER TABLE. There are two ways to write it. An inline constraint is part of a single column's definition, so it can only cover that one column. An out-of-line constraint is a separate clause that names its columns. It works for one column or several, and it is how you add a constraint to columns that already exist. Composite keys therefore have to be written out-of-line. NOT NULL is the reverse case: it can only be written inline.

Give your constraints names with CONSTRAINT <name>. Named constraints are easier to work with later, for example when you change one with ALTER TABLE … ALTER CONSTRAINT. If you leave the name out, the system generates one, and GET_DDL doesn't return that generated name. Also note that GET_DDL always rebuilds primary, unique and foreign keys as out-of-line clauses, even for a single-column key, so the DDL you get back may not look like the DDL you wrote.

ALTER TABLE table1
  ADD COLUMN col3 VARCHAR NOT NULL CONSTRAINT uniq_col3 UNIQUE NOT ENFORCED;

ALTER TABLE table1
  ADD COLUMN col3 VARCHAR NOT NULL CONSTRAINT uniq_col3  ?  NOT ENFORCED;

Sources1

3.Implementing constraints: what Snowflake actually enforces

This is the central point of the topic. A constraint is enforced when it stops a column from being changed in certain ways. Inserting or copying a NULL into a NOT NULL column raises an error, and a row that fails a CHECK expression is rejected. Primary, unique and foreign keys on standard tables are different: they are optional and not enforced. They exist mainly for data modelling purposes and compatibility with other databases, and to support client tools. For example, Tableau can use them for join culling. Snowflake also warns that violated constraints might cause unexpected downstream effects. If something depends on a key, your pipeline has to keep that key valid.

Hybrid tables work the other way. A hybrid table requires a primary key and enforces it. Its UNIQUE and FOREIGN KEY constraints are enforced whenever they are declared, and you can't mark them NOT ENFORCED.

Sources21

4.CHECK constraints and constraint properties

CHECK is the one constraint on a standard table that can enforce a business rule. Snowflake evaluates it on INSERT, UPDATE, MERGE and CTAS. A row is rejected only when the expression evaluates to FALSE. A NULL result lets the row through, so pair CHECK with NOT NULL if the column must always have a value. An inline CHECK can refer only to its own column. A rule that compares two columns has to be written out-of-line. The expression can't use UDFs, subqueries, aggregate functions, or non-deterministic functions such as RANDOM. Some loading paths don't support tables with CHECK constraints. For example, a COPY INTO into such a table fails.

CREATE TABLE test_check_constraint_orders (
  order_id INT,
  quantity INT CHECK (quantity > 0),
  price NUMBER(10, 2));

Key constraints also take properties such as ENFORCED, DEFERRABLE, ENABLE/DISABLE, VALIDATE/NOVALIDATE and RELY/NORELY. Snowflake accepts these to make migration from other databases easier, but on standard tables it does not act on them. The defaults are NOT ENFORCED, DISABLE and NOVALIDATE. One rule catches people out: if you set ENABLE or VALIDATE on a new primary, unique or foreign key, Snowflake does not create the constraint. RELY is the exception, because specifying RELY still creates the constraint. The session parameter UNSUPPORTED_DDL_ACTION decides whether these non-default values raise an error.

Sources13

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.A PRIMARY KEY on a standard Snowflake table blocks duplicate rows.Why is that wrong?

   On standard tables, primary, unique and foreign keys are optional and not enforced. Only NOT NULL and CHECK reject data.

   Covered in Implementing constraints: what Snowflake actually enforces

2. 2.Adding ENABLE or VALIDATE to a primary key makes Snowflake check it.Why is that wrong?

   These are non-default values. On primary, unique and foreign keys they stop the constraint from being created at all. Only RELY still creates it.

   Covered in CHECK constraints and constraint properties

3. 3.A UNIQUE column, like a primary key, cannot contain NULLs.Why is that wrong?

   UNIQUE only guarantees distinct values. NOT NULL comes with PRIMARY KEY, not with UNIQUE.

   Covered in Defining primary keys and the other key types