Relationships and primary keys don't exist in Snowflake

This page has 0 threads | Add post

Blogs

Many people around the world enjoy and benefit from our blogs. If you're one of them - and can afford to - please make a small donation to help keep this and future blogs, exercises, skills assessment tests, tips, tutorials and videos freely available to everyone!

Some other pages relevant to these blogs include:

Snowflake classes

You can also book hourly online consultancy for your time zone with one of our 7 expert trainers!

How does Snowflake compare to Microsoft's T-SQL for SQL Server? Part four of a five-part series of blogs
All dialects of SQL are similar, you'd think, but the different ethos and editing environment of Snowflake versus SQL Server make the switch surprisingly difficult, as this blog explains. How does Snowflake compare to Microsoft's T-SQL for SQL Server? Differences in SQL You write Snowflake in a very different environment Relationships and primary keys don't exist in Snowflake (this blog) Some SQL programming differences

How does Snowflake compare to Microsoft's T-SQL for SQL Server?

Part four of a five-part series of blogs

All dialects of SQL are similar, you'd think, but the different ethos and editing environment of Snowflake versus SQL Server make the switch surprisingly difficult, as this blog explains.

How does Snowflake compare to Microsoft's T-SQL for SQL Server?
Differences in SQL
You write Snowflake in a very different environment
Relationships and primary keys don't exist in Snowflake (this blog)
Some SQL programming differences

Posted by Andy Brown on 28 January 2026

In this blog

Relationships and primary keys in SQL Server
Relationships and primary keys in Snowflake
The implications when loading data
The importance of safe patterns for insertion

So this is the difference in Snowflake which I initially found hard to believe. To explain the difference, let's first look at how things work in SQL Server.

Relationships and primary keys in SQL Server

In SSMS you can visually show the relationship between two tables using a database diagram:

This shows that each author can have many corresponding books (which makes sense).

SSMS is able to create and enforce relationships because tables have one field which is the primary key:

The key symbol next to the AuthorId column shows that it's impossible to have two authors in our databse with the same author id.

SQL Server will always enforce referential integrity (ie ensure that data obeys relationships between tables) and primary keys, so it's impossible to get mismatched data if your database is correctly designed.

Relationships and primary keys in Snowflake

Unbelievably (at least to this SQL Server user) Snowflake has no concept of a primary key or a relationship. So although we know that there's a link between these two tables, Snowflake doesn't:

This is the Author table in Snowflake.

This is the Book table.

Of course, this doesn't stop you joining the two tables together:

SELECT

-- join each author's first and last name

a.FirstName || ' ' || a.LastName AS Author,

b.Title

FROM

Author AS a

JOIN Book AS b ON a.AuthorId = b.BookId

The implications when loading data

Snowflake assumes that the place from which you are getting data is robust. You can ingest data (the Snowflake word for extracting and loading) from many different sources:

Some of the places from which you can import data.

However, it's the providers of data who are responsible for ensuring that you don't have any duplicate key fields or invalid relationship data.

The importance of safe patterns for insertion

One implication of all of the above is that you have to be very careful when adding data, to ensure you don't violate the constraints which you know are there (but Snowflake doesn't). Here for example is an excerpt from some code to merge some books safely into our database:

-- after doing an insert you typically have to check 2 possible cases

-- case 1: the records you're inserting match existing data, in

-- which case we update the existing rows

WHEN MATCHED AND (

tgt.AuthorId IS DISTINCT FROM src.AuthorId

OR tgt.Title IS DISTINCT FROM src.Title

) THEN

UPDATE SET

AuthorId = src.AuthorId,

Title = src.Title

-- case 2: there is no match, so we can insert the rows

WHEN NOT MATCHED THEN

INSERT (BookId, AuthorId, Title)

VALUES (src.BookId, src.AuthorId, src.Title);

All of this sends a shiver down my spine, but it's a necessary consequence of the way the Snowflake database is built.

Parts of this blog
How does Snowflake compare to Microsoft's T-SQL for SQL Server? Differences in SQL You write Snowflake in a very different environment Relationships and primary keys don't exist in Snowflake (this blog) Some SQL programming differences

Some other pages relevant to these blogs include:

Classes in Snowflake

You can also book hourly online consultancy for your time zone with one of our 7 expert trainers!

This blog has 0 threads

Add a new post

ALL BLOGS

SNOWFLAKE BLOGS

SNOWFLAKE BLOGS

Relationships and primary keys in SQL Server

Relationships and primary keys in Snowflake

The implications when loading data

The importance of safe patterns for insertion

Head office

London

Manchester