Welcome To TechBrothersIT: Snowflake SQL Tutorial 105 - PRIMARY KEY, UNIQUE and FOREIGN KEY Are Not Enforced

Snowflake SQL Tutorial 105 - PRIMARY KEY, UNIQUE and FOREIGN KEY Are Not Enforced - Level 9

Snowflake SQL · Episode 105

In this post we are going to cover constraints in Snowflake: PRIMARY KEY, UNIQUE, FOREIGN KEY and NOT NULL.

A constraint is a rule about the data. In most databases you declare one and the database then refuses any row that breaks it.

In Snowflake only one of those four refuses anything. The other three are recorded, described, reported by the catalog, and never checked. This is the most important thing in Level 9, and Snowflake is not hiding it from you — there is a column in the catalog called ENFORCED and it says NO.

We will build a textbook schema, look at what the catalog says about it, and then break every rule in it one at a time. Every query below was run on a real Snowflake account, and every result you see is the output it gave back.

Episode 105 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

On this page

Let's build a textbook schema
Now let's ask Snowflake to describe it
Now let's look at the column that says NO
What happens when two rows claim the same primary key?
And a duplicate in a UNIQUE column?
And a foreign key pointing at nothing?
Now let's delete a parent row, which is worse
So is anything enforced? Let's try NOT NULL
Can we add a constraint after the fact?
Will UNIQUE even look at a column that is already dirty?
What about a second UNIQUE on the same column?
Now the sharp one: a PRIMARY KEY added by ALTER TABLE
One more, and this is the dangerous one: RELY
One last thing: write the checks down yourself

Let's build a textbook schema

Two tables. A parent table of departments and a child table of staff.

The parent has a primary key, a unique name and a not-null code. The child has a primary key, a not-null name, a foreign key pointing at the parent, and a unique email address. There is nothing clever here. It is what you would write in any database.

Sample data

-- Sample data for this post.
-- A textbook schema. Every constraint you would write in any other database.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

CREATE OR REPLACE TABLE DEPTS_T105 (
    DEPT_ID    INT          PRIMARY KEY,
    DEPT_NAME  VARCHAR(40)  UNIQUE,
    DEPT_CODE  VARCHAR(10)  NOT NULL
);

CREATE OR REPLACE TABLE STAFF_T105 (
    STAFF_ID   INT          PRIMARY KEY,
    STAFF_NAME VARCHAR(40)  NOT NULL,
    DEPT_ID    INT          REFERENCES DEPTS_T105(DEPT_ID),
    EMAIL      VARCHAR(60)  UNIQUE
);

INSERT INTO DEPTS_T105 (DEPT_ID, DEPT_NAME, DEPT_CODE)
SELECT 10, 'Engineering', 'ENG'
UNION ALL SELECT 20, 'Finance',   'FIN'
UNION ALL SELECT 30, 'Marketing', 'MKT';

INSERT INTO STAFF_T105 (STAFF_ID, STAFF_NAME, DEPT_ID, EMAIL)
SELECT 1, 'Ana',  10, 'ana@example.com'
UNION ALL SELECT 2, 'Ben',  20, 'ben@example.com'
UNION ALL SELECT 3, 'Cleo', 10, 'cleo@example.com';

All of it compiled and all of it ran. Let's read the data back with a join.

Query

SELECT S.STAFF_ID, S.STAFF_NAME, D.DEPT_NAME
FROM STAFF_T105 S
LEFT JOIN DEPTS_T105 D ON D.DEPT_ID = S.DEPT_ID
ORDER BY S.STAFF_ID;

Result3 rows · 3 columns

STAFF_ID	STAFF_NAME	DEPT_NAME
1	Ana	Engineering
2	Ben	Finance
3	Cleo	Engineering

Three rows, and they all join up. Ana in Engineering, Ben in Finance, Cleo in Engineering. So far this is an ordinary relational schema doing ordinary relational things.

Now let's ask Snowflake to describe it

DESCRIBE TABLE shows the constraints as well as the columns. Let's start with the parent.

Query

DESCRIBE TABLE DEPTS_T105;

Result3 rows · 13 columns · scroll →

name	type	kind	null?	default	primary key	unique key	check	expression	comment	policy name	privacy domain	write default
DEPT_ID	NUMBER(38,0)	COLUMN	N	NULL	Y	N	NULL	NULL	NULL	NULL	NULL	NULL
DEPT_NAME	VARCHAR(40)	COLUMN	Y	NULL	N	Y	NULL	NULL	NULL	NULL	NULL	NULL
DEPT_CODE	VARCHAR(10)	COLUMN	N	NULL	N	N	NULL	NULL	NULL	NULL	NULL	NULL

Three columns, and the constraints are all there. DEPT_ID has Y under primary key. DEPT_NAME has Y under unique key.

Now look at the null? column, because it tells us something the other two columns do not. DEPT_ID says N and we never wrote NOT NULL on it. DEPT_NAME says Y, even though it is unique. DEPT_CODE says N, which is the one we did write.

So a PRIMARY KEY in CREATE TABLE has made that column non-nullable. A UNIQUE has not. Hold on to that, we come back to it twice.

Query

DESCRIBE TABLE STAFF_T105;

Result4 rows · 13 columns · scroll →

name	type	kind	null?	default	primary key	unique key	check	expression	comment	policy name	privacy domain	write default
STAFF_ID	NUMBER(38,0)	COLUMN	N	NULL	Y	N	NULL	NULL	NULL	NULL	NULL	NULL
STAFF_NAME	VARCHAR(40)	COLUMN	N	NULL	N	N	NULL	NULL	NULL	NULL	NULL	NULL
DEPT_ID	NUMBER(38,0)	COLUMN	Y	NULL	N	N	NULL	NULL	NULL	NULL	NULL	NULL
EMAIL	VARCHAR(60)	COLUMN	Y	NULL	N	Y	NULL	NULL	NULL	NULL	NULL	NULL

Four columns, same picture. STAFF_ID is the primary key and says N. STAFF_NAME is our explicit NOT NULL. DEPT_ID, which is the foreign key, is nullable and carries no mark at all in these columns.

Now let's look at the column that says NO

INFORMATION_SCHEMA.TABLE_CONSTRAINTS lists what the account has recorded. Let's ask it about our two tables.

Query

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, ENFORCED
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'PUBLIC'
  AND TABLE_NAME IN ('DEPTS_T105', 'STAFF_T105')
ORDER BY TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME;

Result5 rows · 4 columns

CONSTRAINT_NAME	CONSTRAINT_TYPE	TABLE_NAME	ENFORCED
SYS_CONSTRAINT_b44203f6-84d5-42f7-bc2d-3b893a3af182	PRIMARY KEY	DEPTS_T105	NO
SYS_CONSTRAINT_c4ec3d15-df01-4596-a9db-1231a4ab449c	UNIQUE	DEPTS_T105	NO
SYS_CONSTRAINT_bb2a7fba-620f-499a-a769-eee66b06551c	FOREIGN KEY	STAFF_T105	NO
SYS_CONSTRAINT_7ab4af49-cf0d-4de5-a00a-a5d193d8ffae	PRIMARY KEY	STAFF_T105	NO
SYS_CONSTRAINT_589c8594-ab04-4877-88bb-6e3d557eeea0	UNIQUE	STAFF_T105	NO

Five constraints across the two tables. A primary key and a unique on the parent, and a foreign key, a primary key and a unique on the child. Snowflake accepted all of it and gave each one a generated name.

Now look at the last column.

There is a column called ENFORCED

ENFORCED reads NO on every one of those five rows.
That is not a bug, a setting or an oversight. Snowflake documents these constraints as informational: they are recorded for people reading the schema and for the query optimiser, and no row is ever checked against them.
The rest of this post is just finding out exactly what NO means.

What happens when two rows claim the same primary key?

Staff ID one is Ana. It is the primary key of the table. Let's insert another row that also claims to be staff one.

Query

INSERT INTO STAFF_T105 (STAFF_ID, STAFF_NAME, DEPT_ID, EMAIL)
SELECT 1, 'Dan', 20, 'dan@example.com';

Result1 row · 1 column

number of rows inserted
1

One row inserted. No error, no warning, no mention of the row that already has that key.

So how many rows claim to be staff one now?

Query

SELECT STAFF_ID, COUNT(*) AS ROWS_WITH_THIS_KEY,
       ARRAY_AGG(STAFF_NAME) WITHIN GROUP (ORDER BY STAFF_NAME) AS THE_NAMES
FROM STAFF_T105
GROUP BY STAFF_ID
HAVING COUNT(*) > 1;

Result1 row · 3 columns

STAFF_ID	ROWS_WITH_THIS_KEY	THE_NAMES
1	2	[ "Ana", "Dan" ]

Two, and the names column tells us which two. Ana and Dan.

Query

SELECT STAFF_ID, STAFF_NAME, DEPT_ID, EMAIL FROM STAFF_T105
ORDER BY STAFF_ID, STAFF_NAME;

Result4 rows · 4 columns

STAFF_ID	STAFF_NAME	DEPT_ID	EMAIL
1	Ana	10	ana@example.com
1	Dan	20	dan@example.com
2	Ben	20	ben@example.com
3	Cleo	10	cleo@example.com

Four rows, one Ana and one Dan, and both of them are staff one. There is no sense in which this table has a primary key.

So can a primary key be missing altogether?

That is a different question from whether it can be duplicated, and the catalog has already answered it. Both DESCRIBE grids above put N in the null? column for the primary key, which means the column will not take a NULL.

Let's count what is in there.

Query

SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT(STAFF_ID) AS ROWS_WITH_A_KEY,
       COUNT(*) - COUNT(STAFF_ID) AS ROWS_WITH_A_NULL_KEY
FROM STAFF_T105;

Result1 row · 3 columns

ROWS_TOTAL	ROWS_WITH_A_KEY	ROWS_WITH_A_NULL_KEY
4	4	0

Four rows, four of them with a key, and none at all without one.

What is and is not shown here

The capture file for this episode has no statement that offers a NULL to STAFF_ID, so there is no refusal to show you here and I am not going to describe one.
What is on the page is the catalog saying the column is not nullable, and a count saying no row in it is missing a key. The part of this that we do test directly comes later, when the same constraint is added a different way and the catalog gives a different answer.

So in CREATE TABLE, PRIMARY KEY gets you exactly half of what the words promise. The key cannot be missing. It need not be unique. And the half that survives is the half NOT NULL was already giving you under a different name.

And a duplicate in a UNIQUE column?

Ana's email address is already in the table and the EMAIL column is declared UNIQUE. Let's insert a second Ana with the identical address.

Query

INSERT INTO STAFF_T105 (STAFF_ID, STAFF_NAME, DEPT_ID, EMAIL)
SELECT 4, 'Ana the second', 20, 'ana@example.com';

Result1 row · 1 column

number of rows inserted
1

Accepted. So how unique did that column turn out to be?

Query

SELECT EMAIL, COUNT(*) AS ROWS_WITH_THIS_EMAIL
FROM STAFF_T105
GROUP BY EMAIL
HAVING COUNT(*) > 1
ORDER BY EMAIL;

Result1 row · 2 columns

EMAIL	ROWS_WITH_THIS_EMAIL
ana@example.com	2

The same address on two rows. The constraint is still sitting in the catalog and it did not look at the row going past it.

And a foreign key pointing at nothing?

There are three departments and none of them is department ninety-nine. Let's hire Finn into department ninety-nine.

Query

INSERT INTO STAFF_T105 (STAFF_ID, STAFF_NAME, DEPT_ID, EMAIL)
SELECT 5, 'Finn', 99, 'finn@example.com';

Result1 row · 1 column

number of rows inserted
1

Accepted. And the join shows exactly what we have done.

Query

SELECT S.STAFF_ID, S.STAFF_NAME, S.DEPT_ID, D.DEPT_NAME,
       D.DEPT_ID IS NULL AS NO_SUCH_DEPARTMENT
FROM STAFF_T105 S
LEFT JOIN DEPTS_T105 D ON D.DEPT_ID = S.DEPT_ID
WHERE S.STAFF_ID IS NOT NULL
ORDER BY S.STAFF_ID, S.STAFF_NAME;

Result6 rows · 5 columns

STAFF_ID	STAFF_NAME	DEPT_ID	DEPT_NAME	NO_SUCH_DEPARTMENT
1	Ana	10	Engineering	FALSE
1	Dan	20	Finance	FALSE
2	Ben	20	Finance	FALSE
3	Cleo	10	Engineering	FALSE
4	Ana the second	20	Finance	FALSE
5	Finn	99	NULL	TRUE

Six rows. Finn is on the last one, pointing at department ninety-nine, with a NULL department name because there is nothing to join to, and NO_SUCH_DEPARTMENT marked TRUE.

An orphan, sitting in the table, under a declared foreign key.

Now let's delete a parent row, which is worse

Inserting a bad child row is at least something somebody did on purpose. This next one is the thing that happens by accident.

Department ten is Engineering. Ana and Cleo both point at it. Let's delete it.

Query

DELETE FROM DEPTS_T105 WHERE DEPT_ID = 10;

Result1 row · 1 column

number of rows deleted
1

One row deleted, and no complaint about the children at all. In any enforcing database this statement is either refused or it cascades. Here it just happened.

Let's count the damage.

Query

SELECT COUNT(*) AS STAFF_TOTAL,
       COUNT(D.DEPT_ID) AS STAFF_WITH_A_REAL_DEPARTMENT,
       COUNT(*) - COUNT(D.DEPT_ID) AS STAFF_POINTING_AT_NOTHING
FROM STAFF_T105 S
LEFT JOIN DEPTS_T105 D ON D.DEPT_ID = S.DEPT_ID;

Result1 row · 3 columns

STAFF_TOTAL	STAFF_WITH_A_REAL_DEPARTMENT	STAFF_POINTING_AT_NOTHING
6	3	3

Six staff in the table. Three of them point at a department that exists. Three point at nothing. Half the table is orphaned, and it took one statement.

And the foreign key that was supposed to prevent this?

Query

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, ENFORCED
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = 'STAFF_T105'
ORDER BY CONSTRAINT_TYPE, CONSTRAINT_NAME;

Result3 rows · 3 columns

CONSTRAINT_NAME	CONSTRAINT_TYPE	ENFORCED
SYS_CONSTRAINT_bb2a7fba-620f-499a-a769-eee66b06551c	FOREIGN KEY	NO
SYS_CONSTRAINT_7ab4af49-cf0d-4de5-a00a-a5d193d8ffae	PRIMARY KEY	NO
SYS_CONSTRAINT_589c8594-ab04-4877-88bb-6e3d557eeea0	UNIQUE	NO

Still there. Still listed. Still NO under ENFORCED. It never did anything and it never stopped doing it.

So is anything enforced? Let's try NOT NULL

STAFF_NAME is declared NOT NULL. Let's write a row with no name.

Query

INSERT INTO STAFF_T105 (STAFF_ID, STAFF_NAME, DEPT_ID, EMAIL)
SELECT 6, NULL, 20, 'nobody@example.com';

Query failed — Snowflake returned an error

100072 (22000): DML operation to table STAFF_T105 failed on column STAFF_NAME with error: NULL result in a non-nullable column

Refused. NULL result in a non-nullable column, and the message names the column. This is the first refusal on the page.

Let's make sure it is the column being checked and not the word NULL in our statement. Same insert, with the column left out of the list entirely.

Query

INSERT INTO STAFF_T105 (STAFF_ID, DEPT_ID, EMAIL)
SELECT 7, 20, 'nobody2@example.com';

Query failed — Snowflake returned an error

100072 (22000): DML operation to table STAFF_T105 failed on column STAFF_NAME with error: NULL result in a non-nullable column

The same refusal. Saying nothing about a NOT NULL column is the same as saying NULL, because there is no default to fall back on.

And on the parent table too, so we know it is not something about this one table.

Query

INSERT INTO DEPTS_T105 (DEPT_ID, DEPT_NAME, DEPT_CODE)
SELECT 40, 'Legal', NULL;

Query failed — Snowflake returned an error

100072 (22000): DML operation to table DEPTS_T105 failed on column DEPT_CODE with error: NULL result in a non-nullable column

Refused again. NOT NULL is the one that means it. It always has been, and it is the only one of the four that has ever refused a row in this post.

Can we add a constraint after the fact?

Yes, and you can give it a name of your own instead of the generated one. Let's put a UNIQUE on the department code.

Query

ALTER TABLE DEPTS_T105 ADD CONSTRAINT DEPT_CODE_UNIQUE_T105 UNIQUE (DEPT_CODE);

Result1 row · 1 column

status
Statement executed successfully.

Accepted. Let's see it in the catalog.

Query

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, ENFORCED
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = 'DEPTS_T105'
ORDER BY CONSTRAINT_NAME;

Result3 rows · 3 columns

CONSTRAINT_NAME	CONSTRAINT_TYPE	ENFORCED
DEPT_CODE_UNIQUE_T105	UNIQUE	NO
SYS_CONSTRAINT_b44203f6-84d5-42f7-bc2d-3b893a3af182	PRIMARY KEY	NO
SYS_CONSTRAINT_c4ec3d15-df01-4596-a9db-1231a4ab449c	UNIQUE	NO

There it is, under the name we chose, with ENFORCED still reading NO like all the rest.

So let's put a duplicate into the column it is supposed to be protecting. A second finance department, with the same code.

Query

INSERT INTO DEPTS_T105 (DEPT_ID, DEPT_NAME, DEPT_CODE)
SELECT 50, 'Finance Europe', 'FIN';

Result1 row · 1 column

number of rows inserted
1

Query

SELECT DEPT_CODE, COUNT(*) AS ROWS_WITH_THIS_CODE
FROM DEPTS_T105 GROUP BY DEPT_CODE HAVING COUNT(*) > 1;

Result1 row · 2 columns

DEPT_CODE	ROWS_WITH_THIS_CODE
FIN	2

Two rows with the code FIN, under a UNIQUE constraint we added ourselves a moment ago.

Query

SELECT (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
        WHERE TABLE_NAME = 'DEPTS_T105' AND CONSTRAINT_TYPE = 'UNIQUE')
         AS UNIQUE_CONSTRAINTS_DECLARED,
       (SELECT COUNT(*) FROM (SELECT DEPT_CODE FROM DEPTS_T105
                              GROUP BY DEPT_CODE HAVING COUNT(*) > 1))
         AS DUPLICATED_VALUES_PRESENT;

Result1 row · 2 columns

UNIQUE_CONSTRAINTS_DECLARED	DUPLICATED_VALUES_PRESENT
2	1

The table has two unique constraints declared on it — the one on DEPT_NAME from CREATE TABLE and the one we just added on DEPT_CODE — and a duplicated value present at the same time.

One refusal before we move on. Let's drop a constraint that does not exist.

Query

ALTER TABLE DEPTS_T105 DROP CONSTRAINT NO_SUCH_CONSTRAINT_T105;

Query failed — Snowflake returned an error

001450 (42601): SQL compilation error:
constraint 'NO_SUCH_CONSTRAINT_T105' does not exist

Refused, by name, at compile time. The catalog knows exactly which constraints exist. It just does not act on any of them.

Notice the order of what we just did, though, because it leaves a question open. We added the constraint to a clean column and then dirtied it. That tells us a declared UNIQUE does not stop a duplicate going in. It does not tell us whether Snowflake would have accepted the constraint on a column that was already dirty.

To ask that properly we need a table that was dirty before any constraint existed, which is a different file and the next section.

Will UNIQUE even look at a column that is already dirty?

A fresh table, with nothing declared on it at all, and four rows that are wrong in every way we can manage.

A deliberately dirty table

-- A second table, in a file of its own, so that the data is
-- dirty BEFORE any constraint exists on it.
CREATE OR REPLACE TABLE DIRTY_T105B (
    ID        INT,
    EMAIL     VARCHAR(60),
    DEPT_CODE VARCHAR(10)
);

INSERT INTO DIRTY_T105B (ID, EMAIL, DEPT_CODE)
SELECT 1, 'ana@example.com', 'ENG'
UNION ALL SELECT 1, 'ana@example.com', 'ENG'
UNION ALL SELECT 2, 'ben@example.com', 'FIN'
UNION ALL SELECT NULL, NULL, 'FIN';

Query

SELECT ID, EMAIL, DEPT_CODE FROM DIRTY_T105B ORDER BY ID, EMAIL;

Result4 rows · 3 columns

ID	EMAIL	DEPT_CODE
1	ana@example.com	ENG
1	ana@example.com	ENG
2	ben@example.com	FIN
NULL	NULL	FIN

Four rows. The first two are identical. The last one has no ID and no email at all, and those two cells are a real NULL rather than an empty piece of text.

Query

SELECT COUNT(*)                                        AS ROWS_TOTAL,
       COUNT(*) - COUNT(DISTINCT ID)                   AS DUPLICATE_IDS,
       COUNT(*) - COUNT(ID)                            AS NULL_IDS,
       COUNT(*) - COUNT(DISTINCT EMAIL)                AS DUPLICATE_EMAILS,
       COUNT(*) - COUNT(DISTINCT DEPT_CODE)            AS DUPLICATE_CODES
FROM DIRTY_T105B;

Result1 row · 5 columns

ROWS_TOTAL	DUPLICATE_IDS	NULL_IDS	DUPLICATE_EMAILS	DUPLICATE_CODES
4	2	1	2	2

One row of counts, and every one of them is a count of something a constraint would forbid.

Query

SELECT COUNT(*) AS CONSTRAINTS_ON_THIS_TABLE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = 'DIRTY_T105B';

Result1 row · 1 column

CONSTRAINTS_ON_THIS_TABLE
0

Zero constraints. That is the important part — the mess was made first.

Now let's add a UNIQUE to the email column, which we have just shown holds a duplicate.

Query

ALTER TABLE DIRTY_T105B ADD CONSTRAINT EMAIL_UNIQUE_T105B UNIQUE (EMAIL);

Result1 row · 1 column

status
Statement executed successfully.

Statement executed successfully. It did not even look.

Query

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, ENFORCED
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = 'DIRTY_T105B'
ORDER BY CONSTRAINT_NAME;

Result1 row · 3 columns

CONSTRAINT_NAME	CONSTRAINT_TYPE	ENFORCED
EMAIL_UNIQUE_T105B	UNIQUE	NO

Query

SELECT EMAIL, COUNT(*) AS COPIES
FROM DIRTY_T105B
GROUP BY EMAIL
HAVING COUNT(*) > 1
ORDER BY EMAIL;

Result1 row · 2 columns

EMAIL	COPIES
ana@example.com	2

Query

SELECT (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
        WHERE TABLE_NAME = 'DIRTY_T105B' AND CONSTRAINT_TYPE = 'UNIQUE')
         AS UNIQUE_CONSTRAINTS_DECLARED,
       (SELECT COUNT(*) FROM (SELECT EMAIL FROM DIRTY_T105B
                              GROUP BY EMAIL HAVING COUNT(*) > 1))
         AS DUPLICATED_VALUES_PRESENT;

Result1 row · 2 columns

UNIQUE_CONSTRAINTS_DECLARED	DUPLICATED_VALUES_PRESENT
1	1

A unique constraint declared, and a duplicated value present, in the same table at the same time.

And it keeps going. Let's add a third copy of that address now the constraint is in place.

Query

INSERT INTO DIRTY_T105B (ID, EMAIL, DEPT_CODE)
SELECT 3, 'ana@example.com', 'MKT';

Result1 row · 1 column

number of rows inserted
1

Query

SELECT EMAIL, COUNT(*) AS COPIES
FROM DIRTY_T105B WHERE EMAIL = 'ana@example.com' GROUP BY EMAIL;

Result1 row · 2 columns

EMAIL	COPIES
ana@example.com	3

Three copies of one address in a column declared unique. This is the strongest form of the point this post is making. The constraint is not a weak check. It is not a check.

What about a second UNIQUE on the same column?

This one is worth knowing because the error message sends people looking in the wrong place. First, a unique on a different column, so we have something to compare against.

Query

ALTER TABLE DIRTY_T105B ADD CONSTRAINT CODE_UNIQUE_T105B UNIQUE (DEPT_CODE);

Result1 row · 1 column

status
Statement executed successfully.

Query

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = 'DIRTY_T105B'
ORDER BY CONSTRAINT_NAME;

Result2 rows · 2 columns

CONSTRAINT_NAME	CONSTRAINT_TYPE
CODE_UNIQUE_T105B	UNIQUE
EMAIL_UNIQUE_T105B	UNIQUE

Two unique constraints on the table, one per column. Fine. Now let's add another one to the email column, which already has a unique on it, under a name nobody has used.

Query

ALTER TABLE DIRTY_T105B ADD CONSTRAINT EMAIL_UNIQUE_AGAIN_T105B UNIQUE (EMAIL);

Query failed — Snowflake returned an error

001455 (42601): SQL compilation error:
constraint with the same signature already exists on table 'DIRTY_T105B'

Constraint with the same signature already exists.

Read that carefully. It is not about the data in the column and it is not about the name we chose. A constraint of that kind on that set of columns already exists, so the definition is the duplicate, not any value.

We can prove that is what the message means. Let's drop one and ask again.

Query

ALTER TABLE DIRTY_T105B DROP CONSTRAINT CODE_UNIQUE_T105B;

Result1 row · 1 column

status
Statement executed successfully.

Query

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = 'DIRTY_T105B'
ORDER BY CONSTRAINT_NAME;

Result2 rows · 2 columns

CONSTRAINT_NAME	CONSTRAINT_TYPE
EMAIL_UNIQUE_T105B	UNIQUE
ID_PK_T105B	PRIMARY KEY

Gone. Now the same UNIQUE (DEPT_CODE) again, under a new name.

Query

ALTER TABLE DIRTY_T105B ADD CONSTRAINT CODE_UNIQUE_REBORN_T105B UNIQUE (DEPT_CODE);

Result1 row · 1 column

status
Statement executed successfully.

Accepted. So DROP CONSTRAINT frees the signature, and the earlier refusal really was about the definition rather than the data.

Query

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, ENFORCED
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = 'DIRTY_T105B'
ORDER BY CONSTRAINT_TYPE, CONSTRAINT_NAME;

Result3 rows · 3 columns

CONSTRAINT_NAME	CONSTRAINT_TYPE	ENFORCED
ID_PK_T105B	PRIMARY KEY	NO
CODE_UNIQUE_REBORN_T105B	UNIQUE	NO
EMAIL_UNIQUE_T105B	UNIQUE	NO

Three constraints declared on that table, and ENFORCED is NO on every one.

Now the sharp one: a PRIMARY KEY added by ALTER TABLE

Earlier, PRIMARY KEY in CREATE TABLE made the column non-nullable. The ID column on this dirty table has duplicates and a NULL in it already. Let's make it the primary key.

Query

ALTER TABLE DIRTY_T105B ADD CONSTRAINT ID_PK_T105B PRIMARY KEY (ID);

Result1 row · 1 column

status
Statement executed successfully.

Accepted. Let's check the catalog has it.

Query

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, ENFORCED
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = 'DIRTY_T105B'
ORDER BY CONSTRAINT_TYPE, CONSTRAINT_NAME;

Result3 rows · 3 columns

CONSTRAINT_NAME	CONSTRAINT_TYPE	ENFORCED
ID_PK_T105B	PRIMARY KEY	NO
CODE_UNIQUE_T105B	UNIQUE	NO
EMAIL_UNIQUE_T105B	UNIQUE	NO

Three constraints, and the primary key is one of them. Now the question that matters. Is the column non-nullable, the way the CREATE TABLE version was?

Query

SELECT COLUMN_NAME, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = 'DIRTY_T105B'
ORDER BY ORDINAL_POSITION;

Result3 rows · 2 columns

COLUMN_NAME	IS_NULLABLE
ID	YES
EMAIL	YES
DEPT_CODE	YES

IS_NULLABLE is YES. The primary key column is still nullable.

Query

SELECT COUNT(*)                      AS ROWS_TOTAL,
       COUNT(*) - COUNT(ID)          AS ROWS_WITH_A_NULL_KEY,
       COUNT(*) - COUNT(DISTINCT ID) AS DUPLICATE_KEYS
FROM DIRTY_T105B;

Result1 row · 3 columns

ROWS_TOTAL	ROWS_WITH_A_NULL_KEY	DUPLICATE_KEYS
5	1	2

The NULL that was there before the constraint is still there, and so are the duplicate keys. Nothing was cleaned up and nothing was rejected.

So let's write a brand new row with no key at all, after the primary key was declared.

Query

INSERT INTO DIRTY_T105B (ID, EMAIL, DEPT_CODE) SELECT NULL, 'zoe@example.com', 'ENG';

Result1 row · 1 column

number of rows inserted
1

Query

SELECT COUNT(*)             AS ROWS_TOTAL,
       COUNT(*) - COUNT(ID) AS ROWS_WITH_A_NULL_KEY
FROM DIRTY_T105B;

Result1 row · 2 columns

ROWS_TOTAL	ROWS_WITH_A_NULL_KEY
6	2

One row inserted, and the table now has two rows with no key in a column declared PRIMARY KEY.

The two forms are not the same constraint

Written into CREATE TABLE, PRIMARY KEY makes the column NOT NULL and the catalog's null? column says so.
Added by ALTER TABLE ... ADD CONSTRAINT, the catalog still reports IS_NULLABLE as YES and a new NULL key goes straight in.
Same three words. Two different meanings, decided entirely by where you wrote them.

One last count on this table, with everything declared on it beside everything that is true of it.

Query

SELECT (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
        WHERE TABLE_NAME = 'DIRTY_T105B')            AS CONSTRAINTS_DECLARED,
       (SELECT COUNT(*) FROM DIRTY_T105B)            AS ROWS_IN_TABLE,
       (SELECT COUNT(*) - COUNT(DISTINCT ID) FROM DIRTY_T105B)
                                                     AS DUPLICATE_KEYS,
       (SELECT COUNT(*) - COUNT(ID) FROM DIRTY_T105B) AS NULL_KEYS,
       (SELECT COUNT(*) - COUNT(DISTINCT EMAIL) FROM DIRTY_T105B)
                                                     AS DUPLICATE_EMAILS;

Result1 row · 5 columns

CONSTRAINTS_DECLARED	ROWS_IN_TABLE	DUPLICATE_KEYS	NULL_KEYS	DUPLICATE_EMAILS
3	6	3	2	3

The left-hand number is what the schema claims. Everything to the right of it is what the schema claims cannot happen.

One more, and this is the dangerous one: RELY

There is a keyword you can put on a constraint called RELY. It does not make Snowflake check anything. It tells the query optimiser that it may believe the constraint when it plans a query.

A relied-on key

-- One more table, with RELY on the key.
CREATE OR REPLACE TABLE TRUSTED_T105 (
    ID    INT PRIMARY KEY RELY,
    LABEL VARCHAR(20)
);

INSERT INTO TRUSTED_T105 (ID, LABEL)
SELECT 1, 'first' UNION ALL SELECT 1, 'second';

Two rows, both claiming ID one, under a primary key declared RELY.

Query

INSERT INTO TRUSTED_T105 (ID, LABEL)
SELECT 1, 'first' UNION ALL SELECT 1, 'second';

Result1 row · 1 column

number of rows inserted
2

Both accepted, which by now is what we expect. Let's look at the table.

Query

SELECT ID, LABEL FROM TRUSTED_T105 ORDER BY LABEL;

Result2 rows · 2 columns

ID	LABEL
1	first
1	second

Query

SELECT COUNT(*) AS ROWS_PRESENT, COUNT(DISTINCT ID) AS DISTINCT_IDS
FROM TRUSTED_T105;

Result1 row · 2 columns

ROWS_PRESENT	DISTINCT_IDS
2	1

Two rows present, one distinct ID. The data disagrees with the declaration, and the declaration is the one the planner has been told to trust.

Query

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, ENFORCED
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = 'TRUSTED_T105';

Result1 row · 3 columns

CONSTRAINT_NAME	CONSTRAINT_TYPE	ENFORCED
SYS_CONSTRAINT_39abd205-6b59-4fa8-8d17-deb49c188b12	PRIMARY KEY	NO

ENFORCED is still NO. The constraints view does not mention RELY at all, so let's ask SHOW PRIMARY KEYS, which does.

Query

SHOW PRIMARY KEYS IN TABLE TRUSTED_T105;

Result1 row · 9 columns · scroll →

created_on	database_name	schema_name	table_name	column_name	key_sequence	constraint_name	rely	comment
2026-09-28 13:06:02.639000-07:00	DEMO_DB	PUBLIC	TRUSTED_T105	ID	1	SYS_CONSTRAINT_39abd205-6b59-4fa8-8d17-deb49c188b12	true	NULL

There is a rely column and it reads true. That is the one place on this page the property shows up.

Why RELY is the dangerous one

Think about what this combination means. The constraint is not enforced, so it can be false. And it is relied on, so the optimiser is allowed to plan as though it were true.
An unenforced constraint the planner believes is a more dangerous object than one it ignores. A wrong one can change an answer, not just mislead somebody reading the schema. Use it only where you are checking the data yourself.

One last thing: write the checks down yourself

If the database will not check it, the check still has to exist. It just has to be a query you run. Here are the three that give you back what the constraints were supposed to be doing.

First, duplicate keys.

Query

SELECT STAFF_ID, COUNT(*) AS COPIES
FROM STAFF_T105
WHERE STAFF_ID IS NOT NULL
GROUP BY STAFF_ID
HAVING COUNT(*) > 1
ORDER BY STAFF_ID;

Result1 row · 2 columns

STAFF_ID	COPIES
1	2

Staff one, two copies, which is the row we inserted at the beginning.

Then orphans — child rows pointing at a parent that is not there.

Query

SELECT S.STAFF_ID, S.STAFF_NAME, S.DEPT_ID
FROM STAFF_T105 S
LEFT JOIN DEPTS_T105 D ON D.DEPT_ID = S.DEPT_ID
WHERE S.DEPT_ID IS NOT NULL AND D.DEPT_ID IS NULL
ORDER BY S.STAFF_ID;

Result3 rows · 3 columns

STAFF_ID	STAFF_NAME	DEPT_ID
1	Ana	10
3	Cleo	10
5	Finn	99

Three rows. Ana and Cleo lost their department when we deleted it, and Finn never had one.

And then the one to actually put on a schedule. One row, three numbers, and it tells you whether the table can be trusted.

Query

SELECT (SELECT COUNT(*) FROM (SELECT STAFF_ID FROM STAFF_T105
                              WHERE STAFF_ID IS NOT NULL
                              GROUP BY STAFF_ID HAVING COUNT(*) > 1))
         AS DUPLICATE_KEYS,
       (SELECT COUNT(*) FROM STAFF_T105 WHERE STAFF_ID IS NULL)
         AS MISSING_KEYS,
       (SELECT COUNT(*) FROM STAFF_T105 S
        LEFT JOIN DEPTS_T105 D ON D.DEPT_ID = S.DEPT_ID
        WHERE S.DEPT_ID IS NOT NULL AND D.DEPT_ID IS NULL)
         AS ORPHANS;

Result1 row · 3 columns

DUPLICATE_KEYS	MISSING_KEYS	ORPHANS
1	0	3

One duplicated key, no missing keys, three orphans. Run that after every load and you have your constraints back, as an alert instead of a refusal.

Declare them, and check them yourself

Declare the constraints anyway. They document the schema for the next person, the optimiser can use them, and a tool reading the catalog will draw you the right diagram.
Just never assume they are checking anything, because they are not, and the catalog has been telling you so in a column called ENFORCED the whole time.

Finally, let's clean up after ourselves.

Cleanup

-- Put the schema back the way we found it.
DROP TABLE IF EXISTS STAFF_T105;
DROP TABLE IF EXISTS DEPTS_T105;
DROP TABLE IF EXISTS TRUSTED_T105;
DROP TABLE IF EXISTS DIRTY_T105B;

Query

SELECT CURRENT_DATABASE()                 AS THE_DATABASE,
       CURRENT_SCHEMA()                   AS THE_SCHEMA,
       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
        WHERE RIGHT(TABLE_NAME, 5) = '_T105') AS PROBE_TABLES_LEFT,
       (SELECT COUNT(*) FROM CUSTOMERS)   AS DEMO_DB_IS_READABLE;

Result1 row · 4 columns

THE_DATABASE	THE_SCHEMA	PROBE_TABLES_LEFT	DEMO_DB_IS_READABLE
DEMO_DB	PUBLIC	0	10

No probe tables left, and the demo database still reads. Back where we started.

Key points

Snowflake accepts PRIMARY KEY, UNIQUE and FOREIGN KEY, records them, and describes them. It does not enforce them, and the catalog says so in a column called ENFORCED.
A second row with the same primary key is accepted. A duplicate in a UNIQUE column is accepted. A row pointing at a parent that does not exist is accepted.
Deleting a parent row orphans its children silently, and the foreign key stays in the catalog throughout.
UNIQUE can be added to a column that already holds duplicates. It does not look at the data it is making a claim about.
A PRIMARY KEY written into CREATE TABLE makes the column non-nullable — the null? column in DESCRIBE says N without us writing NOT NULL.
A PRIMARY KEY added by ALTER TABLE does not. IS_NULLABLE stays YES and a new NULL key goes straight in. Same words, two meanings.
NOT NULL is the only one of the four that refuses a row, and leaving the column out of the insert is the same as writing NULL into it.
A second UNIQUE on the same column is refused for its signature, not its data, and DROP CONSTRAINT frees that signature again.
RELY tells the optimiser it may believe a constraint nothing is checking. A wrong one can change an answer, not just mislead a reader.
If the database will not check it, write the check down. One query gives you duplicate keys, missing keys and orphans.

Next in this series

That closes the first half of Level 9. In the next post we will look at DROP, UNDROP and TRUNCATE — three ways to get rid of data, and only one of them is reversible.

Every query and every result on this page was run on a Snowflake account (version 10.34.101). The departments and staff are made up for teaching. Nothing here is timed and no performance claims are made anywhere in this series. The constraint behaviour is documented in the Snowflake constraints documentation.

Welcome To TechBrothersIT

Label

Snowflake SQL Tutorial 105 - PRIMARY KEY, UNIQUE and FOREIGN KEY Are Not Enforced - Level 9

Let's build a textbook schema

Now let's ask Snowflake to describe it

Now let's look at the column that says NO

What happens when two rows claim the same primary key?

So can a primary key be missing altogether?

And a duplicate in a UNIQUE column?

And a foreign key pointing at nothing?

Now let's delete a parent row, which is worse

So is anything enforced? Let's try NOT NULL

Can we add a constraint after the fact?

Will UNIQUE even look at a column that is already dirty?

What about a second UNIQUE on the same column?

Now the sharp one: a PRIMARY KEY added by ALTER TABLE

One more, and this is the dangerous one: RELY

One last thing: write the checks down yourself

Key points

No comments:

Post a Comment