Constraint Properties¶
Constraint properties are specified in the CONSTRAINT clause for a CREATE TABLE or ALTER TABLE command.
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. Note that
NOT NULL
is the only constraint enforced by Snowflake. See Referential Integrity Constraints.{ 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.
By default, this constraint property is set to
DISABLE
.{ 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.By default, this constraint property is set to
NOVALIDATE
.{ RELY | NORELY }
Specifies whether a constraint in
NOVALIDATE
mode is taken into account during query rewrite.By default, this constraint property is set to
NORELY
.If you have ensured that the data in the table does comply with the constraints, you can change this to
RELY
to indicate that the query optimizer should expect the data in the table to adhere to the constraints. Setting theRELY
property can improve query performance (e.g. by eliminating unnecessary joins).For primary and foreign key constraints, set this property on both the primary key constraint and the foreign key constraint. For example:
ALTER TABLE table_with_primary_key ALTER CONSTRAINT a_primary_key_constraint RELY; ALTER TABLE table_with_foreign_key ALTER CONSTRAINT a_foreign_key_constraint RELY;
Non-Default Values for the ENABLE
and VALIDATE
Constraint Properties¶
For syntax compatibility with other databases, Snowflake supports specifying non-default values for the constraint properties.
However, if you specify ENABLE
or VALIDATE
(the non-default values for these properties) when creating a new
constraint, the constraint is not created. This does not apply to RELY
. Specifying RELY
does result in
the creation of the new constraint.
Note that Snowflake provides a session parameter, UNSUPPORTED_DDL_ACTION, which determines if specifying non-default values during constraint creation generates an error.