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.
Related Articles,
- Snowflake Set Operators: UNION, EXCEPT/MINUS and INTERSECT
- Snowflake Regular Expression Functions and Examples
Hope this helps 🙂