FOREIGN KEY

Applies to

• SQL Server

• Azure Synapse Analytics

Description

The syntax for the Foreign Key is fully supported by SnowFlake, except for the [ NOT FOR REPLICATION ] and the WITH CHECK clauses.

Syntax in SQL Server

Review the following SQL Server documentation for more information.

[ FOREIGN KEY ]  
REFERENCES [ schema_name . ] referenced_table_name  
[ ( ref_column ) ]  
[ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]  
[ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]  
[ NOT FOR REPLICATION ]

Syntax in Snowflake

[ FOREIGN KEY ]
REFERENCES <ref_table_name> [ ( <ref_col_name> ) ]
[ MATCH { FULL | SIMPLE | PARTIAL } ]
[ ON [ UPDATE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION } ]
     [ DELETE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION } ] ]
[ [ NOT ] ENFORCED ]
[ [ NOT ] DEFERRABLE ]
[ INITIALLY { DEFERRED | IMMEDIATE } ]
[ ENABLE | DISABLE ]
[ VALIDATE | NOVALIDATE ]
[ RELY | NORELY ]

Sample Source Patterns

General case

SQL Server

IN -> SqlServer_01.sql

ALTER TABLE dbo.student 
ADD CONSTRAINT Fk_empid FOREIGN KEY(emp_id)
REFERENCES dbo.emp(id);

ALTER TABLE dbo.student 
ADD CONSTRAINT Fk_empid FOREIGN KEY(emp_id)
REFERENCES dbo.emp(id)
NOT FOR REPLICATION;

Snowflake

OUT -> SqlServer_01.sql

ALTER TABLE dbo.student
ADD CONSTRAINT Fk_empid FOREIGN KEY(emp_id)
REFERENCES dbo.emp (id);

ALTER TABLE dbo.student
ADD CONSTRAINT Fk_empid FOREIGN KEY(emp_id)
REFERENCES dbo.emp (id);

WITH CHECK / NO CHECK case

Notice that Snowflake logic does not support the CHECK clause in the creation of foreign keys. The WITH CHECK statement is marked as not supported. Besides, the WITH NO CHECK clause is removed because it is the default behavior in Snowflake and the equivalence is the same.

Please, review the following examples to have a better understanding of the translation.

SQL Server

ALTER TABLE testTable
WITH CHECK ADD CONSTRAINT testFK1 FOREIGN KEY (table_id)
REFERENCES otherTable (Othertable_id);

ALTER TABLE testTable
WITH NOCHECK ADD CONSTRAINT testFK2 FOREIGN KEY (table_id)
REFERENCES otherTable (Othertable_id);

Snowflake

ALTER TABLE testTable
----** SSC-FDM-0014 - CHECK STATEMENT NOT SUPPORTED **
--WITH CHECK
           ADD CONSTRAINT testFK1 FOREIGN KEY (table_id)
REFERENCES otherTable (Othertable_id);


ALTER TABLE testTable
ADD CONSTRAINT testFK2 FOREIGN KEY (table_id)
REFERENCES otherTable (Othertable_id);

Known Issues

1. NOT FOR REPLICATION clause.

Snowflake has a different approach to the replication cases. Please, review the following documentation.

2. WITH CHECK clause.

Snowflake does not support the WITH CHECK statement. Review the following documentation for more information.

Related EWIs

No related EWIs.