Overview of Constraints¶
Snowflake provides the following constraint functionality:
- Unique, primary, and foreign keys, and NOT NULL columns.
- Named constraints.
- Single-column and multi-column constraints.
- Creation of constraints inline and out-of-line.
- Support for creation, modification and deletion of constraints.
In this Topic:
Supported Constraint Types¶
Snowflake supports the following constraint types from the ANSI SQL standard:
- PRIMARY KEY
- FOREIGN KEY
- NOT NULL
A table can have multiple unique keys and foreign keys, but only one primary key. All foreign keys must reference a corresponding primary or unique key that matches the column types of each column in the foreign key. The primary key for a foreign key can be on a different table or the same table as the foreign key.
Snowflake supports defining and maintaining constraints, but does not enforce them, except for NOT NULL constraints, which are always enforced.
Snowflake supports defining constraints on permanent, transient, and temporary tables. Constraints can be defined on columns of all data types, and there are no limits on the number of columns that can be included in a constraint.
When a table is copied using CREATE TABLE … LIKE or CREATE TABLE … CLONE, all existing constraints on the table, including foreign keys, are copied to the new table.
Additional commands and functions, such as DROP/UNDROP and GET_DDL are supported for tables with constraints. They are also supported for schemas and databases.
For Snowflake Time Travel, when previous versions of a table are copied, the current version of the constraints on the table are used because Snowflake does not store previous versions of constraints in table metadata.
Single-Column and Multi-Column Constraints¶
Constraints can be defined on a single column or on multiple columns in the same table.
For multi-column constraints (i.e. compound primary keys or unique keys), the columns are ordered, and each column has a corresponding key sequence.
Inline and Out-of-Line Constraints¶
Constraints are defined either inline or out-of-line during table creation or modification:
- Inline constraints are created as part of the column definition and can only be used for single-column constraints.
- Out-of-line constraints are defined using a separate clause that specifies the column(s) on which the constraint is created. They can be used for creating either single-column or multi-column constraints, as well as creating constraints for existing columns.