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;
Copy

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.

Language: English