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 this 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 specifies whether specifying non-default values during constraint creation generates an error.