FOREIGN KEY

Description

The syntax for the Foreign Key is fully supported by SnowFlake, except for the [ NOT FOR REPLICATION ] clause.

Syntax in SQL Server

[ 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

SQL Server

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

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

ALTER TABLE IF EXISTS dbo.student
ADD CONSTRAINT Fk_empid FOREIGN KEY(emp_id)
REFERENCES dbo.emp (id)
-- ** MSC-WARNING - MSCEWI1042 - Commented NOT FOR REPLICATION - THIS IS NON-RELEVANT **
--NOT FOR REPLICATION
;

Known Issues

1. NOT FOR REPLICATION clause is not supported in Snowflake.

The entire NOT FOR REPLICATION clause is commented out since it is not supported in Snowflake.

Related EWIs

• MSCEWI1042: NON-RELEVANT SYNTAX.