Overview of constraints¶
Snowflake provides the following constraint functionality:
- Constraint types from the ANSI SQL standard. For more information, see Supported constraint types.
- Named constraints.
- Single-column and multi-column constraints.
- Creation of constraints inline and out-of-line.
- Creation, modification, and deletion of constraints.
For more information, see CREATE | ALTER TABLE … CONSTRAINT.
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 propertyerror. - 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.
| Feature | Hybrid tables | Standard tables |
|---|---|---|
| PRIMARY KEY constraints | Required, enforced | Optional, not enforced |
| FOREIGN KEY constraints | Optional, enforced (referential integrity) | Optional, not enforced |
| UNIQUE constraints | Optional, enforced | Optional, not enforced |
| NOT NULL constraints | Optional, enforced | Optional, enforced |
| CHECK constraints | Optional, enforced; can be defined only at table creation | Optional, enforced |
Why standard tables don’t enforce constraints¶
Snowflake doesn’t enforce PRIMARY KEY, UNIQUE, and FOREIGN KEY constraints on standard tables. Checking these constraints on very large, distributed, columnar tables would slow down data loading and DML operations. NOT NULL and CHECK constraints are enforced.
Even though they aren’t enforced, these constraints are still useful as metadata and documentation:
- You can migrate DDL from other databases without removing the constraint definitions.
- BI and data modeling tools can read the constraints to discover keys and relationships between tables.
- The constraints describe how your tables relate to each other for people who work with the data.
For more information about defining constraints, see CREATE | ALTER TABLE … CONSTRAINT.
If you set the RELY property on a constraint, the optimizer can use it for join elimination. Set RELY only if you’ve ensured that the data complies with the constraint. For more information, see Setting the RELY Constraint Property to Eliminate Unnecessary Joins.
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.
-
For hybrid tables, adding a UNIQUE or FOREIGN KEY constraint with ALTER TABLE is an online operation that also validates the rows already in the table. For more information, see Add and drop constraints on an existing hybrid 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 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 NULLandDEFAULT, 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:
You can show information about existing CHECK constraints by querying the CHECK_CONSTRAINTS view.
CHECK constraints are enforced during the following SQL operations:
- INSERT
- UPDATE
- MERGE
- CREATE TABLE … AS SELECT (CTAS)
- COPY INTO <table>, including the loads that Snowpipe runs
If the condition evaluates to TRUE or NULL, the row is written. If the condition
evaluates to FALSE, the CHECK constraint fails.
For examples of CHECK constraints, see Examples of constraints with standard tables. For an example that creates a table with a CHECK constraint and loads files into it, see Turn on error logging and add CHECK constraints.
Usage notes¶
-
Check constraints are always enforced.
-
You can use the following ALTER TABLE commands and the Iceberg equivalents to work with CHECK constraints:
-
ALTER TABLE … RENAME CONSTRAINT
-
ALTER TABLE … ADD [ CONSTRAINT <constraint_name> ] CHECK ( <expr> ) ENABLE NOVALIDATE
- ENABLE VALIDATE is the default for CHECK constraints, but this command doesn’t support it. To add a CHECK
constraint to an existing table with this command, specify ENABLE NOVALIDATE. Otherwise, the statement fails
with the error
ALTER TABLE ADD CHECK is not supported with ENABLE VALIDATE. - ENABLE NOVALIDATE enforces the constraint for all rows that are inserted, updated, or loaded after you run the command, but doesn’t enforce the constraint for existing rows.
- ENABLE VALIDATE is the default for CHECK constraints, but this command doesn’t support it. To add a CHECK
constraint to an existing table with this command, specify ENABLE NOVALIDATE. Otherwise, the statement fails
with the error
-
ALTER TABLE … ALTER CONSTRAINT <constraint_name> ENABLE NOVALIDATE
You can’t use ALTER CONSTRAINT to change a CHECK constraint to ENABLE VALIDATE, so a constraint that you added with ENABLE NOVALIDATE can’t be changed later to check existing rows. To find existing rows that violate the constraint, query the table for rows where
NOT (<expr>)isTRUE. -
ALTER TABLE … DROP CONSTRAINT
-
-
The following ALTER TABLE commands and Iceberg equivalents can operate on a column with a CHECK constraint defined on it:
-
ALTER TABLE … ALTER COLUMN
Only operations that don’t modify a CHECK constraint are supported.
-
ALTER TABLE … RENAME COLUMN
Check constraints that reference the renamed column are implicitly updated to use the new column name.
-
ALTER TABLE … DROP COLUMN
The operation fails if the column being dropped is used by an existing CHECK constraint that also references another column. In this case, delete the constraint before deleting the column.
-
-
When a row violates a CHECK constraint, what happens next depends on the operation:
- An
INSERT,UPDATE,MERGE, or CTAS statement fails the first time it encounters a row that violates the constraint. If error logging is turned on for the table, the statement records the row in the table’s error table instead of failing. For a CTAS statement, error logging is turned on if the statement setsERROR_LOGGING = TRUE. For more information, see Error logging for DML statements. - A
COPY INTOstatement, including one that Snowpipe runs, rejects the row and handles it according to theON_ERRORcopy option, the same way it handles other errors in a file. For example, withON_ERROR = CONTINUE, the other rows in the file are loaded. WithON_ERROR = SKIP_FILE, which is the default for Snowpipe, the file isn’t loaded. WithON_ERROR = ABORT_STATEMENT, which is the default when you runCOPY INTOdirectly and isn’t supported for pipes, the statement fails. To record every rejected row in a table that you can query, turn on error logging for the target table and useON_ERROR = CONTINUE. Error logging supports CSV, JSON, and Parquet files. For more information, see Error logging for COPY INTO and Snowpipe. To find the rows that violate CHECK constraints in the error table, see Find rows that violate CHECK constraints. The VALIDATE and VALIDATE_PIPE_LOAD functions and theVALIDATION_MODEparameter ofCOPY INTOdon’t return rows that violate CHECK constraints.
- An
-
For hybrid tables, you can define a CHECK constraint only when you create the table. ALTER TABLE … ADD CONSTRAINT … CHECK and ALTER TABLE … ALTER CONSTRAINT aren’t supported on a hybrid table. You can rename or drop an existing CHECK constraint on a hybrid table, but you can’t add it back afterward. When you load a hybrid table with
COPY INTO, a row that violates a CHECK constraint causes the load to fail, becauseCOPY INTOsupports onlyON_ERROR = ABORT_STATEMENTfor hybrid tables. Hybrid tables don’t support error logging, so anINSERT,UPDATE, orMERGEstatement that writes such a row also fails. For more information, see CHECK constraints for hybrid tables.
Limitations¶
- Standard tables, hybrid tables, and Snowflake-managed Iceberg tables support CHECK constraints. Other types of tables don’t support CHECK constraints.
- The expression associated with an existing CHECK constraint can’t be modified using an ALTER TABLE command. To modify the expression, drop and re-create the CHECK constraint. On a hybrid table, you can’t re-create the constraint on the existing table, so you must re-create the table.
- CHECK constraints can’t be specified in CREATE OR ALTER TABLE commands.
- You can’t add a CHECK constraint to a table that any pipe loads, including a pipe for Snowpipe Streaming with
high-performance architecture, even if the pipe is paused. If you try, the statement fails with the error
Cannot add CHECK constraint to table ... because it has active pipes. To add the constraint, you must drop the pipes and create them again. To do this without skipping any file or loading any file twice, follow the steps in Change the CHECK constraints on a table that a pipe loads. - You can’t drop a CHECK constraint or change it with ALTER TABLE … ALTER CONSTRAINT on a table that a Snowpipe pipe
loads, even if the pipe is paused. If you try, the statement fails with the error
Cannot drop or alter CHECK constraint on table ... because it has active pipes. Pipes for Snowpipe Streaming with high-performance architecture don’t block these changes. To drop or change the constraint, follow the same steps in Change the CHECK constraints on a table that a pipe loads. - The following operations don’t support CHECK constraints:
- Snowpipe Streaming with the classic architecture doesn’t support tables with CHECK constraints. If you attempt to open a channel on such a table, the operation fails. Snowpipe Streaming with high-performance architecture supports CHECK constraints and enforces them during ingestion.
- If you attempt external writes on Iceberg tables that have CHECK constraints, the operation fails.