Constraint Properties

Constraint properties are specified in the CONSTRAINT clause for a CREATE TABLE or ALTER TABLE command.

In this Topic:

ANSI SQL Constraint Properties

Properties for All Constraints

The following constraint properties from the ANSI SQL standard apply to all constraints (unique, primary key, and foreign key):

ENFORCED | NOT ENFORCED
Specifies whether the constraint is enforced in a transaction.
DEFERRABLE | NOT DEFERRABLE
Specifies whether, in subsequent transactions, the constraint check can be deferred until the end of the transaction.
INITIALLY { DEFERRED | IMMEDIATE }
For DEFERRABLE constraints, specifies whether the check for the constraints are deferred starting from the next transaction.

The defaults for the standard constraint properties are:

NOT ENFORCED DEFERRABLE INITIALLY DEFERRED

Properties for Foreign Key Constraints Only

The following constraint properties are also from the ANSI SQL standard, but only apply to foreign keys:

MATCH { FULL | PARTIAL | SIMPLE }
Specifies whether the foreign key is satisfied with regard to NULL values in one or more of the columns.
UPDATE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION }
Specifies the action performed when the primary/unique key for the foreign key is updated.
DELETE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION }
Specifies the action performed when the primary/unique key for the foreign key is deleted.

The defaults for the foreign key constraint properties are:

MATCH FULL UPDATE NO ACTION DELETE NO ACTION

Extended Constraint Properties

The following properties are not part of the ANSI SQL standard, but are supported for compatibility with other databases. They apply to all constraints (unique, primary key, and foreign key):

ENABLE | DISABLE
Specifies whether the constraint is enabled or disabled. These properties are provided for compatibility with Oracle.
VALIDATE | NOVALIDATE
Specifies whether to validate existing data on the table when a constraint is created. Only used in conjunction with the ENABLE | DISABLE property.
RELY | NORELY
Specifies whether a constraint in NOVALIDATE mode is taken into account during query rewrite.

The defaults for the extended constraint properties are:

DISABLE NOVALIDATE RELY

Non-Default Values for Constraint Properties

For compatibility with other databases, Snowflake supports specifying non-default values for the constraint properties; however, if non-default values are specified during creation of a constraint, the constraint is not created.

Note that Snowflake provides a session parameter, UNSUPPORTED_DDL_ACTION, which specifies whether specifying non-default values during constraint creation generates an error.