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

[Phil-Garmann]

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

[MCRE-BE]

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

[Phil-Garmann]

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

[Phil-Garmann]

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

[MCRE-BE]

I like the idea

[Phil-Garmann]

It seems like the this was implemented with #1296

[MCRE-BE]

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

[Pixelements]

@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!

[MCRE-BE]

@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?

[Phil-Garmann]

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

[Phil-Garmann]

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

[sd-db]

@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)