Support for the RELY property of primary and foreign key constraint

Describe the feature

Add native support for the RELY property of primary and foreign key constraint, by adding an optional, boolean input to the constraint types.

Optional feature extension - QoL safeguard

Setting rely: true would implicitly create the not_null and unique constraints to ensure that the constraint is valid/satisfied. This is purely a safeguard against incorrect query results when the rely option is set but not satisfied.

Describe alternatives you've considered

The behavior is already achievable for primary constraints, as they can be implemented directly as expression using the custom constraint type (introduced in #792). However, for foreign key constraints this is not a viable option as the ref macro is not a part of the limited Jinja context for .yaml files.

Who will this benefit?

One of the main reasons for adding PK/FK constraints is for the potential performance enhancement, which requires the RELY option to be set. Hence, most (if not all) users of these constraints on this adapter would benefit from this feature.

Furthermore, the RELY constraint property is also used by snowflake, extending the appeal and useability of the feature.

Are you interested in contributing this feature?

Yes

I was exactly trying to use this feature and failing. Good to see why.
Hope it gets implemented !

Mockup

constraints.py

@dataclass
class TypedConstraint(ModelLevelConstraint, ABC):
    """Constraint that enforces type because it has render logic"""

    str_type: ClassVar[str]
    rely: bool | None = None

class PrimaryKeyConstraint(TypedConstraint):
    str_type = "primary_key"

    def _validate(self) -> None:
        if not self.columns:
            raise self._render_error([["columns"]])

    def _render_suffix(self) -> str:
        suffix = f"PRIMARY KEY ({', '.join(self.columns)})"
        if self.expression:
            suffix += f" {self.expression}"
        if self.rely:
            suffix += " RELY"
        return suffix

class ForeignKeyConstraint(TypedConstraint):
    str_type = "foreign_key"

    def _validate(self) -> None:
        if not self.columns or (not (self.to_columns and self.to) and not self.expression):
            raise self._render_error(
                [["columns", "to", "to_columns"], ["columns", "expression"]],
            )

    def _render_suffix(self) -> str:
        suffix = f"FOREIGN KEY ({', '.join(self.columns)})"
        if self.expression:
            suffix += f" {self.expression}"
        else:
            suffix += f" REFERENCES {self.to} ({', '.join(self.to_columns)})"
        if self.rely:
            suffix += " RELY"
        return suffix

test_constraints.py

def test_primary_key_constraint_render__with_rely(self):
    constraint = PrimaryKeyConstraint(
        type=ConstraintType.primary_key, columns=["id"], rely=True
    )
    assert constraint.render() == "PRIMARY KEY (id) RELY"

test_constraint_macros.py

def test_macros_get_constraint_sql_primary_key_rely(self, template_bundle, model):
    constraint = {
        "type": "primary_key",
        "name": "myconstraint",
        "columns": ["name"],
        "rely": True,
    }
    r = self.render_constraint_sql(template_bundle, constraint, model)

    expected = (
        "['alter table `some_database`.`some_schema`.`some_table` add constraint "
        "myconstraint primary key(`name`) rely;']"
    )
    assert expected in r

Or alternatively, the functionality can be achieved by simply refactoring ForeignKeyConstraint such that expression is used for constraints options similar to the current implementation of PrimaryKeyConstraint:

class ForeignKeyConstraint(TypedConstraint):
    str_type = "foreign_key"

    def _validate(self) -> None:
        if not self.columns or (not (self.to_columns and self.to) and not self.expression):
            raise self._render_error(
                [["columns", "to", "to_columns"], ["columns", "expression"]],
            )

    def _render_suffix(self) -> str:
        suffix = (
            f"FOREIGN KEY ({', '.join(self.columns)})"
            f" REFERENCES {self.to} ({', '.join(self.to_columns)})"
        )

        if self.expression:
            suffix += f" {self.expression}"
        return suffix

I like the idea

It seems like the this was implemented with #1296

@Phil-Garmann

@Phil-Garmann : thanks for figuring that one out.
I got it working

@Phil-Garmann : thanks for figuring that one out. I got it working

Could you post the schema.yml definition of your primary_key constraint definition? I can't get it working. I've tried "expression: rely" & "expression: "rely". Thank you so much!

@Phil-Garmann : thanks for figuring that one out. I got it working

Could you post the schema.yml definition of your primary_key constraint definition? I can't get it working. I've tried "expression: rely" & "expression: "rely". Thank you so much!

For Primary Key's

          - type: primary_key
            expression: RELY

For Foriegn Key's

          - type: foreign_key
           name: fk__ref_table_name__ref_column_name # OPTIONAL
            to: ref('ref_table_name')
            to_columns: [ref_column_name]
            expression: REFERENCES ref_table_name (ref_column_name) RELY

So I guess it's case sensitive?

@Pixelements

@Pixelements my bad; this was only partly solved by #1296...

@sd-db

@sd-db any chance that this will be picked up soon? 🤞

@Phil-Garmann

@Phil-Garmann I had an initial PR ready, but currently busy with core-v2 work. Since this is a large change, it would take time (probably next month)

@sd-db

@sd-db any news? And would the changes be included in v2/fusion as well? 👀

One data point on the "QoL safeguard" in the original proposal, which may matter for the PR in progress: on Databricks, dbt's built-in unique test does not detect duplicates once the primary key is declared RELY. A safeguard built on that test would pass on the data it is meant to catch.

Repro (dbt-core 1.12.3, dbt-databricks 1.12.5, serverless SQL warehouse, DBSQL 2026.36, Photon)

Two table models select the same six rows from a source where order_id = 3 appears twice. Their contracts differ only in the PK option, set with the expression: RELY workaround from earlier in this thread:

- name: orders_rely
  config:
    contract: {enforced: true}
  constraints:
    - type: primary_key
      columns: [order_id]
      expression: RELY        # NORELY on orders_norely
  columns:
    - name: order_id
      data_type: bigint
      constraints: [{type: not_null}]
      data_tests: [unique]

dbt build:

3 of 4 FAIL 1 unique_orders_norely_order_id .................................... [FAIL 1 in 1.70s]
4 of 4 PASS unique_orders_rely_order_id ........................................ [PASS in 1.54s]

Why: the test compiles to group by order_id having count(*) > 1. With RELY, the optimizer treats order_id as unique, so every group has one row, and it plans the query as LocalTableScan <empty>. Query history shows 0 rows and 0 files read. On a 1,000,010-row table (16 files, 10 duplicated keys), the NORELY run found all 10 duplicate keys and the RELY run found none. The docs say RELY rewrites need Photon; I only tested on Photon.

A window-based test still catches it. With this override in the project, the RELY model's test fails with 2 rows:

{% macro databricks__test_unique(model, column_name) %}
select unique_field, n_records
from (
    select
        {{ column_name }} as unique_field,
        count(*) over (partition by {{ column_name }}) as n_records
    from {{ model }}
    where {{ column_name }} is not null
) counted
where n_records > 1
{% endmacro %}

What this means for the safeguard

A unique data test that runs after the constraint exists, which is the normal dbt build order, passes on violating data.
A unique constraint doesn't help either, because Databricks does not enforce UNIQUE constraints.
Two approaches that would work: validate uniqueness before the constraint is declared with RELY, or have the adapter's unique test count with a window when RELY is set. The window version held in my runs, but I found no documentation promising the optimizer will leave it alone.

Write-up with the plans and query-history evidence: https://community.databricks.com/t5/community-articles/declaring-a-primary-key-rely-turned-my-dbt-unique-test-green-on/m-p/169864

@sd-db

@sd-db should I port this issue to the main dbt repo for better traceability for the v2 development?

Navigation Menu

Repository navigation

Support for the `RELY` property of primary and foreign key constraint #1271

Describe the feature

Optional feature extension - QoL safeguard

Describe alternatives you've considered

Who will this benefit?

Are you interested in contributing this feature?

Mockup

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Support for the RELY property of primary and foreign key constraint #1271

Description

Describe the feature

Optional feature extension - QoL safeguard

Describe alternatives you've considered

Who will this benefit?

Are you interested in contributing this feature?

Activity

MCRE-BE commented on Dec 26, 2025

Phil-Garmann commented on Jan 5, 2026

Mockup

Phil-Garmann commented on Jan 6, 2026

MCRE-BE commented on Mar 18, 2026

Phil-Garmann commented on Mar 19, 2026

MCRE-BE commented on Mar 20, 2026

Pixelements commented on May 26, 2026

MCRE-BE commented on May 27, 2026

Phil-Garmann commented on May 27, 2026

Phil-Garmann commented on Jul 31, 2026

sd-db commented on Aug 13, 2026

Phil-Garmann commented on Sep 11, 2026

ivanvyd commented on Sep 25, 2026

Phil-Garmann commented on Oct 19, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

Support for the `RELY` property of primary and foreign key constraint #1271