What you will be able to do
- Declare a foreign key from a child table to a parent table's primary key
- Choose between INNER and LEFT OUTER joins when joining parent and child tables
- Explain how RELY lets Snowflake eliminate unnecessary joins, and the risk that comes with it
1.Linking child to parent with a foreign key
In a parent/child model, the parent table owns the key (for example, an order) and the child table repeats it (for example, the order's lines). A FOREIGN KEY constraint records that link. Every foreign key has to reference a primary key or unique key whose column types match, and that key can be in another table or in the same table. On a standard Snowflake table the foreign key is declared but not enforced. Snowflake will accept child rows that point to a parent that doesn't exist.
With a composite parent key, column order matters. The REFERENCES clause has to list the columns in the same order the primary key used. If you list them in a different order, Snowflake does not create the foreign key. Privileges matter too. To create a foreign key, your role needs OWNERSHIP on the child (foreign key) table and the REFERENCES privilege on the parent table.
CREATE TABLE table2 (
col1 INTEGER NOT NULL,
col2 INTEGER NOT NULL,
CONSTRAINT pkey_1 PRIMARY KEY (col1, col2) NOT ENFORCED
);
CREATE TABLE table3 (
col_a INTEGER NOT NULL,
col_b INTEGER NOT NULL,
CONSTRAINT fkey_1 FOREIGN KEY (col_a, col_b) REFERENCES table2 (col1, col2) NOT ENFORCED
);Copying tables also carries constraints. CREATE TABLE … LIKE, CREATE TABLE … CLONE and cloning a whole schema all copy them. The foreign key relationship depends on what you copy. If you clone parent and child together in the same command, the new child points to the new parent. If you copy only the child, its foreign key still points to the original parent. If you copy only the parent, it keeps its primary key, but no foreign keys are created.
Checkpoint 1 of 6· Check yourself
A parent table has PRIMARY KEY (c_1, c_2). A developer writes the child table's foreign key as FOREIGN KEY (x, y) REFERENCES parent (c_2, c_1). What happens?
The REFERENCES list has to follow the primary key's column order. This check happens when the constraint is created, even though the key is not enforced on data afterwards.
“the columns in the REFERENCES clause must be listed in the same order as they were listed for the primary key.”Source: docs.snowflake.com
Checkpoint 2 of 6· Exam question
A data analyst loads daily order extracts into a standard Snowflake table `sales.orders` whose `order_id` primary key is not enforced. Replays of extract files must not create duplicate orders. Select TWO approaches that keep `order_id` unique without changing the table type.(Select 2)
Correct answers: A, B — Load the files into a staging table first, then run `MERGE INTO sales.orders` keyed on `order_id` with a `WHEN NOT MATCHED THEN INSERT` clause only.; Insert from staging using `QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY loaded_at DESC) = 1` after excluding keys already present in the target.
- A. Correct: a MERGE that only inserts unmatched keys applies the uniqueness rule inside the load itself, so replayed rows find a match and are not inserted again.
- B. Correct: deduplicating the staged rows with ROW_NUMBER and filtering out keys already in the target guarantees that only one new row per `order_id` is inserted.
- C. Incorrect: Snowflake has no `ENFORCED` keyword for standard-table key constraints, so this statement is not valid and would not add enforcement.
- D. Incorrect: `RELY` only tells the optimizer it may trust the key for query rewrites. It never validates or rejects rows during a load.
- E. Incorrect: no `CONSTRAINT_ENFORCEMENT` session parameter exists, and standard-table keys stay informational regardless of session settings.
2.Performing parent/child joins
A foreign key documents how the tables relate, but you still write the join yourself. Snowflake recommends putting the relationship in an ON condition in the FROM clause, such as child.parent_id = parent.id. If the key column has the same name and meaning in both tables, USING(key_column) does the same thing and returns the key column only once.
Which join type you pick decides which rows survive. An inner join returns a row for each parent/child pair that matches the ON condition. A LEFT OUTER JOIN with the parent on the left also keeps parents that have no children, and fills the child columns with NULL. Since the foreign key isn't enforced, orphan child rows can exist on a standard table. An inner join from child to parent drops them without telling you. An outer join keeps them, so you can see them as rows with NULL parent columns. Always include the join condition. Leaving out ON gives a Cartesian product, which pairs every parent row with every child row. Snowflake notes this is often a user error.
| Join | Rows returned |
|---|---|
| o1 INNER JOIN o2 | One row per matching pair under the ON condition |
| o1 LEFT OUTER JOIN o2 | Inner result plus each unmatched o1 row, with o2 columns NULL |
| o1 RIGHT OUTER JOIN o2 | Inner result plus each unmatched o2 row, with o1 columns NULL |
| o1 FULL OUTER JOIN o2 | Joined rows plus unmatched rows from both sides |
Checkpoint 3 of 6· Check yourself
You need every customer (parent), including customers with no orders (child). Which join, with CUSTOMERS on the left, gives that?
A LEFT OUTER JOIN adds a row for each left-side row that has no match, with NULLs on the right. Leaving out ON would give a Cartesian product.
“The result of the inner join is augmented with a row for each row of o1 that has no matches in o2.”Source: docs.snowflake.com
Sources4
3.RELY: letting the optimizer trust your keys
Unenforced keys can still help performance. If your pipeline really does keep primary and foreign keys valid, you can set the RELY property on them. RELY tells the optimizer it may assume the data follows the constraints. The optimizer can then remove joins it doesn't need. For example, a join to a parent table that contributes no columns can be dropped. Snowflake does this only for constraints marked RELY. The default is NORELY. For a parent/child pair, set RELY on both the primary key and the foreign key.
Checkpoint 4 of 6· Fill the gap
Which property completes this example so the optimizer may use the parent/child keys during query rewrite?
ALTER TABLE table_with_primary_key ALTER CONSTRAINT a_primary_key_constraint ? ; ALTER TABLE table_with_foreign_key ALTER CONSTRAINT a_foreign_key_constraint RELY;Set RELY on both related constraints. NORELY is the default and stops the optimizer from using the key. ENFORCED and VALIDATE have no effect on key constraints in standard tables.
Source: docs.snowflake.comThe cost is correctness risk. Snowflake still doesn't check the data. If the keys are violated, for example a duplicate parent key or an orphan child row, a query with RELY can return different results than the same query with NORELY. DML and CTAS statements can also write incorrect data. Only use RELY when your loading process guarantees the keys hold.
Checkpoint 5 of 6· Check yourself
A team sets RELY on PK/FK constraints of standard tables, but a late-arriving load creates duplicate parent keys. What is the risk?
RELY does not enforce anything. It only lets the optimizer trust the key. If the key is violated, results can change and DML or CTAS can write incorrect data.
“If the integrity of your constraints is not maintained, the query results might differ if the RELY constraint property is set”Source: docs.snowflake.com
Checkpoint 6 of 6· Exam question
A BI dashboard joins `fact_sales` to `dim_store` with a `LEFT JOIN` on `store_id` but selects only columns from `fact_sales`. The analyst has verified that `store_id` is unique in `dim_store` and wants Snowflake to be able to skip the unnecessary join. Which action allows this optimization?
Correct answer: C — Declare a primary key on `dim_store (store_id)` with the `RELY` property so the optimizer may treat the column as unique and eliminate the join.
- A. Incorrect: clustering improves pruning but does not tell the optimizer that the join key is unique, so the join still runs.
- B. Incorrect: non-nullable fact columns alone do not prove that the dimension side has at most one match per row, which is what outer join elimination depends on.
- C. Correct: with `RELY` on the dimension's primary key, the optimizer trusts the uniqueness metadata, sees that the left join cannot change the row count, and drops the join.
- D. Incorrect: search optimization speeds selective lookups but has no bearing on whether a join can be removed from the plan.
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.Setting RELY makes Snowflake enforce the primary and foreign keys.Why is that wrong?
RELY only tells the optimizer to trust the keys. You still have to enforce them, and a violated key under RELY can produce incorrect results.
Covered in RELY: letting the optimizer trust your keys
2.Cloning only the parent table also re-creates the foreign keys that point to it.Why is that wrong?
When only the referenced (parent) table is copied, its primary and unique keys are copied, but no new foreign keys are created.
Covered in Linking child to parent with a foreign key
Practise it for real
Create a parent/child pair with a composite primary key and a matching foreign key, then mark both constraints RELY.
1.Run the CREATE TABLE statements for table2 (the parent with PRIMARY KEY (col1, col2)) and table3 (the child with FOREIGN KEY (col_a, col_b) REFERENCES table2 (col1, col2)).
Why: The composite key has to be declared out-of-line, and the foreign key has to reference the primary key columns in the same order.
You should see: Both tables are created, each with its named constraint.
2.Run a second CREATE TABLE for a child table whose foreign key references table2 (col2, col1).
Why: This shows that column order is checked when the foreign key is created.
You should see: Creating the foreign key fails because the column order differs from the primary key.
3.Insert two rows into table2 with the same (col1, col2) values.
Why: This shows that a primary key on a standard table is not enforced.
You should see: Both rows are inserted. Only NOT NULL and CHECK reject data on a standard table.
4.Delete the duplicate row, then run ALTER TABLE … ALTER CONSTRAINT … RELY on pkey_1 in table2 and on fkey_1 in table3.
Why: RELY only makes sense once the data really satisfies the keys, and you set it on both related constraints.
You should see: Both constraints now have RELY set, so the optimizer may eliminate unnecessary joins between the tables.
Stuck? Get a nudge
If step 2 succeeds, compare the REFERENCES column list with the order in pkey_1.
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“All foreign keys must reference a corresponding primary or unique key that matches the column types of each column in the foreign key.”
↩︎ Linking child to parent with a foreign key - 2.
“You must use a role that has the REFERENCES privilege on the unique or primary key table.”
↩︎ Linking child to parent with a foreign key“For related PRIMARY KEY and FOREIGN KEY constraints, set this property on both constraints.”
↩︎ RELY: letting the optimizer trust your keys“If the RELY property is set for a constraint and a violation of referential integrity occurs, DML and CTAS statements might insert incorrect data.”
↩︎ RELY: letting the optimizer trust your keys“For standard tables, it is your responsibility to enforce RELY constraints; otherwise, you might risk unintended behavior and unexpected results.”
↩︎ Exam trap 1“the columns in the REFERENCES clause must be listed in the same order as they were listed for the primary key.”
↩︎ Checkpoint - 3.
“If only the referencing table is copied, a new foreign key is created on the referencing table”
↩︎ Linking child to parent with a foreign key“If only the referenced table is copied, no new foreign keys are created, although the primary or unique keys are copied.”
↩︎ Exam trap 2 - 4.
“the recommended way to join tables is to use JOIN with the ON subclause of the FROM clause”
↩︎ Performing parent/child joins“omitting the ON clause results in a Cartesian product; every row of object_ref1 paired with every row of object_ref2.”
↩︎ Performing parent/child joins“The columns must have the same name and meaning in each of the tables being joined.”
↩︎ Performing parent/child joins“If the word JOIN is used without specifying INNER or OUTER, then the JOIN is an inner join.”
↩︎ Prediction“The result of the inner join is augmented with a row for each row of o1 that has no matches in o2.”
↩︎ Checkpoint - 5.
“These optimizations are performed only if you use the RELY constraint property to indicate that the data in your tables complies with the constraints”
↩︎ RELY: letting the optimizer trust your keys“If the integrity of your constraints is not maintained, the query results might differ if the RELY constraint property is set”
↩︎ Checkpoint