Skip to main content

constraints

Constraints are a feature of many data platforms. When specified, the platform will perform additional validation on data as it is being populated in a new table or inserted into a preexisting table. If the validation fails, the table creation or update fails, the operation is rolled back, and you will see a clear error message.

When enforced, a constraint guarantees that you will never see invalid data in the table materialized by your model. Enforcement varies significantly by data platform.

Constraints require the declaration and enforcement of a model contract.

Constraints are never applied on ephemeral models or those materialized as view. Only table and incremental models support applying and enforcing constraints.

Defining constraints

Constraints may be defined for a single column, or at the model level for one or more columns. As a general rule, we recommend defining single-column constraints directly on those columns.

If you are defining multiple primary_key constraints for a single model, those must be defined at the model level. Defining multiple primary_key constraints at the column level is not supported.

The structure of a constraint is:

  • type (required): one of not_null, unique, primary_key, foreign_key, check, custom
  • expression: Free text input to qualify the constraint. Required for certain constraint types, and optional for others.
  • name (optional): Human-friendly name for this constraint. Supported by some data platforms.
  • columns (model-level only): List of column names to apply the constraint over.

Foreign key constraints accept two additional inputs:

  • to: A relation input, likely ref(), indicating the referenced table.
  • to_columns: A list of column(s) in that table containing the corresponding primary or unique key.

This syntax for defining foreign keys uses ref, meaning it will capture dependencies and works across different environments. It is available in dbt Cloud Versionless, and versions of dbt Core starting with v1.9.

Platform-specific support

In transactional databases, it is possible to define "constraints" on the allowed values of certain columns, stricter than just the data type of those values. For example, Postgres supports and enforces all the constraints in the ANSI SQL standard (not null, unique, primary key, foreign key), plus a flexible row-level check constraint that evaluates to a boolean expression.

Most analytical data platforms support and enforce a not null constraint, but they either do not support or do not enforce the rest. It is sometimes still desirable to add an "informational" constraint, knowing it is not enforced, for the purpose of integrating with legacy data catalog or entity-relation diagram tools (dbt-core#3295). Some data platforms can optionally use primary or foreign key constraints for query optimization if you specify an additional keyword.

To that end, there are two optional fields you can specify on any filter:

  • warn_unenforced: False to skip warning on constraints that are supported, but not enforced, by this data platform. The constraint will be included in templated DDL.
  • warn_unsupported: False to skip warning on constraints that aren't supported by this data platform, and therefore won't be included in templated DDL.
  • PostgreSQL constraints documentation: here
models/constraints_example.sql
{{
config(
materialized = "table"
)
}}

select
1 as id,
'My Favorite Customer' as customer_name,
cast('2019-01-01' as date) as first_transaction_date
models/schema.yml
models:
- name: dim_customers
config:
contract:
enforced: true
columns:
- name: id
data_type: int
constraints:
- type: not_null
- type: primary_key
- type: check
expression: "id > 0"
- name: customer_name
data_type: text
- name: first_transaction_date
data_type: date

Expected DDL to enforce constraints:

target/run/.../constraints_example.sql
create table "database_name"."schema_name"."constraints_example__dbt_tmp"
(
id integer not null primary key check (id > 0),
customer_name text,
first_transaction_date date
)
;
insert into "database_name"."schema_name"."constraints_example__dbt_tmp"
(
id,
customer_name,
first_transaction_date
)
(
select
1 as id,
'My Favorite Customer' as customer_name,
cast('2019-01-01' as date) as first_transaction_date
);
0