Snowflake Foreign Key Constraint and Syntax

Post author:Vithal S
Post last modified:December 20, 2019
Post category:Snowflake
Reading time:5 mins read

Similar to the primary key constraint, the Snowflake foreign key constraint is also information only. It is not enforced when you insert the data into a table. Constraints other than NOT NULL are created as disabled.

Snowflake Foreign Key Constraint

You can create the foreign key while creating tables on the Snowflake cloud data warehouse. But, foreign key will not be enforced when you load the tables. However, constraints provide valuable metadata. The optimizer uses the foreign keys to create an optimal execution plan.

A table can have multiple unique keys and foreign keys. A table can refer multiple tables. But, a column can refer single table.

Snowflake Primary Key Constraint Syntax

There are many methods that you can use to add foreign keys on Snowflake table.

Column level Foreign Key – Add foreign key constraint during table creation.
Table Level Foreign Key – You can specify the foreign key after all columns are defined.
Alter Table to Foreign Key – User Alter table command to add foreign key constraint.

Column Level Foreign Key

You can add the foreign on the column level.

For example, consider below DDL for column level foreign key.

CREATE TABLE fk_demo_table
  ( 
     id      INT references pk_demo_table1(id), 
     NAME    VARCHAR(10), 
     address VARCHAR(100) 
  );

Table Level Foreign Key

You can also add the foreign key on table level.

For example,

CREATE TABLE fk_demo_table
  ( 
     id      INT , 
     NAME    VARCHAR(10), 
     address VARCHAR(100),
	 foreign key (id) references pk_demo_table1(id)
  );

Alter Table to Add Snowflake Foreign Key

You can use ALTER TABLE command to add foreign key.

For example, consider below alter statement to add foreign key to existing Snowflake DDL.

ALTER TABLE fk_demo_table ADD FOREIGN KEY (id) REFERENCES pk_demo_table1(id);

Check Foreign Key

In Snowflake, foreign key constraint is not enforced. You can insert values in tables that are not present in the parent table or reference table.

The parent table contains:

+----+------+---------+
| ID | NAME | ADDRESS |
|----+------+---------|
|  1 | a    | abc     |
|  2 | b    | bcd     |
|  1 | a    | abc     |
+----+------+---------+

Below statement will insert record to the table;

insert into fk_demo_table values (100,'A2A','ZZZ');

Hence, the foreign key constraint is not enforced.