Categories:

Table, View, & Sequence DDL

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.

In this Topic:

Syntax

Inline Unique / Primary / Foreign Key

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

ALTER TABLE <name> ADD COLUMN <col_name> <col_type> { inlineUniquePK | inlineFK }

Where:

inlineUniquePK

inlineUniquePK ::=
  [ NOT NULL ]
  [ CONSTRAINT <constraint_name> ]
  { UNIQUE | PRIMARY KEY }
  [ [ NOT ] ENFORCED ]
  [ [ NOT ] DEFERRABLE ]
  [ INITIALLY { DEFERRED | IMMEDIATE } ]
  [ ENABLE | DISABLE ]
  [ VALIDATE | NOVALIDATE ]
  [ RELY | NORELY ]

inlineFK

inlineFK :=
  [ NOT NULL ]
  [ 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 ]

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 }

Where:

outoflineUniquePK

outoflineUniquePK ::=
  [ CONSTRAINT <constraint_name> ]
  { UNIQUE | PRIMARY KEY } ( <col_name> [ , <col_name> , ... ] )
  [ [ NOT ] ENFORCED ]
  [ [ NOT ] DEFERRABLE ]
  [ INITIALLY { DEFERRED | IMMEDIATE } ]
  [ ENABLE | DISABLE ]
  [ VALIDATE | NOVALIDATE ]
  [ RELY | NORELY ]

outoflineFK

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 ]

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
  • 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 ENABLE NOVALIDATE RELY

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 } ]
  • 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 column(s) exactly match.