CREATE | ALTER TABLE … CONSTRAINT

This topic describes how to create constraints by specifying a CONSTRAINT clause in a CREATE TABLE or ALTER TABLE statement:

  • An inline constraint is specified as part of the individual column definition.

  • An out-of-line constraint is specified as an independent clause:

    • When creating a table, the clause is part of the column definitions for the table.

    • When altering a table, the clause is specified as an explicit ADD action for the table.

For more information, including detailed examples, see Constraints.

Syntax

Inline Unique / Primary / Foreign Key

CREATE TABLE <name> ( <col1_name> <col1_type>    [ NOT NULL ] { inlineUniquePK | inlineFK }
                     [ , <col2_name> <col2_type> [ NOT NULL ] { inlineUniquePK | inlineFK } ]
                     [ , ... ] )

ALTER TABLE <name> ADD COLUMN <col_name> <col_type> [ NOT NULL ] { inlineUniquePK | inlineFK }
Copy

Where:

inlineUniquePK ::=
  [ CONSTRAINT <constraint_name> ]
  { UNIQUE | PRIMARY KEY }
  [ [ NOT ] ENFORCED ]
  [ [ NOT ] DEFERRABLE ]
  [ INITIALLY { DEFERRED | IMMEDIATE } ]
  [ { ENABLE | DISABLE } ]
  [ { VALIDATE | NOVALIDATE } ]
  [ { RELY | NORELY } ]
Copy
inlineFK :=
  [ CONSTRAINT <constraint_name> ]
  [ 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 } ]
Copy

Out-of-Line Unique / Primary / Foreign Key

CREATE TABLE <name> ... ( <col1_name> <col1_type>
                         [ , <col2_name> <col2_type> , ... ]
                         [ , { outoflineUniquePK | outoflineFK } ]
                         [ , { outoflineUniquePK | outoflineFK } ]
                         [ , ... ] )

ALTER TABLE <name> ... ADD { outoflineUniquePK | outoflineFK }
Copy

Where:

outoflineUniquePK ::=
  [ CONSTRAINT <constraint_name> ]
  { UNIQUE | PRIMARY KEY } ( <col_name> [ , <col_name> , ... ] )
  [ [ NOT ] ENFORCED ]
  [ [ NOT ] DEFERRABLE ]
  [ INITIALLY { DEFERRED | IMMEDIATE } ]
  [ { ENABLE | DISABLE } ]
  [ { VALIDATE | NOVALIDATE } ]
  [ { RELY | NORELY } ]
Copy
outoflineFK :=
  [ CONSTRAINT <constraint_name> ]
  FOREIGN KEY ( <col_name> [ , <col_name> , ... ] )
  REFERENCES <ref_table_name> [ ( <ref_col_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 } ]
Copy

Constraint Properties

For compatibility with other databases, Snowflake provides constraint properties. The properties that can be specified for a constraint depend on the type:

  • Some properties apply to all keys (unique, primary, and foreign).

  • Other properties apply only to foreign keys.

Important

These properties are provided to facilitate migrating from other databases. They are not enforced or maintained by Snowflake. This means that the defaults can be changed for these properties, but changing the defaults results in Snowflake not creating the constraint.

For more details, see Constraint Properties.

Properties (for All Keys)

The following constraint properties apply to all keys (the order of the properties is interchangeable):

[ NOT ] ENFORCED
[ NOT ] DEFERRABLE
INITIALLY { DEFERRED | IMMEDIATE }
{ ENABLE | DISABLE }
{ VALIDATE | NOVALIDATE }
{ RELY | NORELY }
Copy
  • { VALIDATE | NOVALIDATE } only applies when either { ENFORCED | NOT ENFORCED } or { ENABLE | DISABLE } is specified.

  • { RELY | NORELY } only applies when NOVALIDATE is specified.

  • Defaults:

    NOT ENFORCED DEFERRABLE INITIALLY DEFERRED DISABLE NOVALIDATE NORELY

Properties (for Foreign Keys Only)

The following constraint properties apply only to foreign keys (the order of the properties is interchangeable):

MATCH { FULL | SIMPLE | PARTIAL }
ON [ UPDATE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION } ]
   [ DELETE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION } ]
Copy
  • MATCH ... specifies whether the foreign key constraint is satisfied with regard to NULL values in one or more of the columns.

  • ON ... specifies the actions performed when the primary key for a foreign key is updated or deleted.

  • Defaults:

    MATCH FULL UPDATE NO ACTION DELETE NO ACTION

Usage Notes

  • NOT NULL specifies that the column does not allow NULL values:

    • This is the only constraint enforced by Snowflake. See Referential Integrity Constraints.

    • It can be specified only as an inline constraint within the column definition.

    • The default is to allow NULL values in columns.

  • Multi-column constraints (e.g. compound unique or primary keys) can only be defined out-of-line.

  • When defining foreign keys, either inline or out-of-line, column name(s) for the referenced table do not need to be specified if the signature (i.e. name and data type) of the foreign key column(s) and the referenced table’s primary key column(s) exactly match.

  • If you create a foreign key, then the columns in the REFERENCES clause must be listed in the same order as the columns were listed for the primary key. For example:

    create table parent ... constraint primary_key_1 primary key (c_1, c_2) ...
    create table child  ... constraint foreign_key_1 foreign key (...) REFERENCES parent (c_1, c_2) ...
    
    Copy

    In both cases, the order of the columns is c_1, c_2. If the order of the columns in the foreign key had been different (e.g. c_2, c_1), then the attempt to create the foreign key would have failed.

Examples

The example below shows how to create a simple NOT NULL constraint while creating a table, and another NOT NULL constraint while altering a table:

Create a table and create a constraint at the same time:

CREATE TABLE table1 (
    col1 INTEGER NOT NULL
    );
Copy

Alter the table to add a column with a constraint:

ALTER TABLE table1 
    ADD COLUMN col2 VARCHAR NOT NULL;
Copy

The following example specifies that the intent of the column is to hold unique values, but makes clear that the constraint is not actually enforced. This example also demonstrates how to specify a name for the constraint (“uniq_col3” in this case.)

ALTER TABLE table1 
    ADD COLUMN col3 VARCHAR NOT NULL CONSTRAINT uniq_col3 UNIQUE NOT ENFORCED;
Copy

The following creates a parent table with a primary key constraint and another table with a foreign key constraint that points to the same columns as the first table’s primary key constraint.

CREATE TABLE table2 (
    col1 INTEGER NOT NULL,
    col2 INTEGER NOT NULL,
    CONSTRAINT pkey_1 PRIMARY KEY (col1, col2) NOT ENFORCED
    );
CREATE TABLE table3 (
    col_a INTEGER NOT NULL,
    col_b INTEGER NOT NULL,
    CONSTRAINT fkey_1 FOREIGN KEY (col_a, col_b) REFERENCES table2 (col1, col2) NOT ENFORCED
    );
Copy