Foreign Key Constraint

Description

These clause specify a foreign key constraint, which requires that a group of one or more columns of the new table must only contain values that match values in the referenced column(s) of some row of the referenced table.

Click here to navigate to the PostgreSQL documentation page for this syntax.

The foreign key constraint is fully supported by Snowflake.

Grammar Syntax

[ CONSTRAINT constraint_name ]
FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
    [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
    [ ON DELETE referential_action ] [ ON UPDATE referential_action ]
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

referential_action in a FOREIGN KEY constraint is:

{ NO ACTION | RESTRICT | CASCADE | SET NULL [ ( column_name [, ... ] ) ] |
 SET DEFAULT [ ( column_name [, ... ] ) ] }

Sample Source Patterns

PostgreSQL

CREATE TABLE contacts(
   contact_id int,
   customer_id int,
   CONSTRAINT fk_customer FOREIGN KEY(customer_id) REFERENCES customers(customer_id) 
      ON DELETE SET NULL
);

Snowflake

CREATE TABLE contacts (
   contact_id int,
   customer_id int,
   CONSTRAINT fk_customer FOREIGN KEY(customer_id) REFERENCES customers (customer_id)
      ON DELETE SET NULL
);