Create Foreign Key command syntax in Snowflake

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

There are three ways to create a Foreign Key in Snowflake:

• Create Column Level Inline Foreign Key Constraint
• Create Table Level Foreign Key Constraint
• Alter table to add Foreign Key to an existing table

Create Column Level Inline Foreign Key Constraint:

CREATE TABLE Employee_table
  ( 
     id      INT REFERENCES SALARY_TABLE(id), 
     NAME    VARCHAR(10), 
     address VARCHAR(100) 
  ); 

Create Table Level Foreign Key Constraint:

CREATE TABLE Employee_table
  ( 
     id      INT, 
     NAME    VARCHAR(10), 
     address VARCHAR(100),
     CONSTRAINT FK_EMP_ID FOREIGN KEY(id) REFERENCES SALARY_TABLE(id)
  ); 

Alter table to add Foreign Key to an existing table

ALTER TABLE Employee_table ADD FOREIGN KEY (id) REFERENCES SALARY_TABLE(id);  

nVector

posted on 05 Apr 20