Overview of constraints¶

Supported constraint types¶

Snowflake supports the following constraint types from the ANSI SQL standard:

• PRIMARY KEY: Guarantees that all of the values in a column are distinct and that the column can’t store NULL values. The primary key uniquely identifies each row in a table.

• UNIQUE: Guarantees that all of the values in a column are distinct. Unlike a PRIMARY KEY constraint, a column with a UNIQUE constraint can have NULL values.

• FOREIGN KEY: Enforces referential integrity by requiring values in a column or set of columns to match values in another table or the same table.

• NOT NULL: Ensures that a column can’t store a NULL value.

• CHECK: Enforces a SQL expression as a condition on the values that can be inserted into or updated in one or more columns of a table. For more information, see CHECK constraints.

A table can have multiple unique keys and foreign keys, but only one primary key. A PRIMARY KEY constraint implies that the column is both NOT NULL and UNIQUE.

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. When you define FOREIGN KEY constraints across hybrid tables, the tables must be in the same database.

The following table summarizes the differences in behavior between standard tables and hybrid tables, with respect to the enforcement of constraints and whether constraints are required:

• A constraint is enforced when it protects a column from being updated in certain ways. For example, a column that is declared NOT NULL can’t contain a NULL value. An attempt to copy or insert a NULL value into a NOT NULL column results in an error. For hybrid tables, you can’t set the NOT ENFORCED property on PRIMARY KEY, FOREIGN KEY, and UNIQUE constraints. Setting this property results in an invalid constraint property error.

• A constraint is required when one or more columns in a table must have such a constraint, which is only true for PRIMARY KEY constraints on hybrid tables.

Table constraints¶

Snowflake supports constraints on permanent, transient, temporary, and hybrid tables. You can define constraints on columns of all data types, and you can include any number of columns in a single constraint.

The following are considerations for constraints:

• When you copy a table by using CREATE TABLE … LIKE or CREATE TABLE … CLONE, all existing constraints on the table, including foreign keys, are copied to the new table. CREATE TABLE … CLONE isn’t supported for hybrid tables.

• 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 doesn’t store previous versions of constraints in table metadata.

Single-column and multi-column constraints¶

You can define constraints on a single column or on multiple columns in the same table.

For multi-column constraints (composite 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 or columns on which the constraint is created. They can be used for creating either single-column or multi-column constraints, as well as for creating constraints on existing columns.

Constraints in GET_DDL¶

The SQL statements that GET_DDL returns include the clauses that define constraints; however, note the following:

• Single-column constraints, such as NOT NULL and DEFAULT, are reconstructed inline with the definition of the column.

• Table constraints, such as unique, primary, and foreign keys, are always reconstructed as out-of-line constraints, even if they consist of a single column.

• For unnamed constraints — that is, constraints with a system-generated name — GET_DDL doesn’t return the system-generated name.

CHECK constraints¶

A CHECK constraint enforces a SQL expression as a condition on the values that can be inserted into or updated in one or more columns of a table. For example, a CHECK constraint might ensure that the quantity column in a table only contains values that are greater than zero or that the salary column in a table only contains values in a specific range.

You can specify a CHECK constraint by using CONSTRAINT clause in the following SQL commands:

• CREATE TABLE

• ALTER TABLE

• CREATE ICEBERG TABLE

• ALTER ICEBERG TABLE

You can show information about existing CHECK constraints by querying the CHECK_CONSTRAINTS view.

Check constraints are enforced during the following DML operations:

• INSERT

• UPDATE

• MERGE

• CREATE TABLE … AS SELECT (CTAS)

If the condition evaluates to TRUE or NULL, the DML operation proceeds. If the condition evaluates to FALSE, the CHECK constraint fails.

For examples of CHECK constraints, see Examples of constraints with standard tables.