foreign_key_exists uses cached_val.constraint_name instead of constraint_name, failing to detect existing FKs

[paperlinguist]

The snowflake__foreign_key_exists macro in snowflake__create_constraints.sql (lines 273 and 306) returns cached_val.constraint_name, but the cache dict only contains columns and rely keys — there is no constraint_name key. This returns Jinja Undefined, causing the caller (snowflake__create_foreign_key, line 97) to evaluate none == existing_constraint as true and attempt to re-create a FK that already exists.

The equivalent function snowflake__unique_constraint_exists (lines 200, 253) correctly uses the loop variable constraint_name.

Steps to reproduce:

1. Run a full dbt build (models + tests) — constraints are created successfully on fresh tables
2. Run a test-only job (dbt test) against the same target — the on-run-end hook fires, SHOW IMPORTED KEYS correctly finds existing FKs, but the buggy return value causes re-creation attempts
3. Snowflake returns: 002002 (42710): SQL compilation error: Object '<constraint_name>' already exists.

Expected behavior: foreign_key_exists should detect existing FKs and skip creation, as unique_constraint_exists does.

Detailed Explanation:

The foreign_key_exists macro iterates through cached constraints using for constraint_name, cached_val in lookup_cache.foreign_keys[table_relation].items(). In this loop, constraint_name is the dict key (the actual constraint name string, e.g. MY_TABLE_MY_COLUMN_FK), while cached_val is the dict value containing only columns and rely:

{
    "MY_TABLE_MY_COLUMN_FK": {   # <- this is constraint_name (loop variable)
        "columns": ["MY_COLUMN"],  # <- cached_val.columns exists
        "rely": "true"             # <- cached_val.rely exists
                                   # <- cached_val.constraint_name does NOT exist
    }
}

When the macro returns cached_val.constraint_name, Jinja resolves this to Undefined since that key doesn't exist. The calling macro in snowflake__create_foreign_key then evaluates none == Undefined, treats the FK as non-existent, and proceeds to create a duplicate constraint.

The caller (snowflake__create_foreign_key, lines 96-97) uses the return value only to check existence:

{%- set existing_constraint = dbt_constraints.foreign_key_exists(fk_table_relation, fk_column_names, lookup_cache) -%}
{%- if none == existing_constraint -%}
    {# none means FK doesn't exist -> create it (line 104) #}
{%- else -%}
    {# non-none means FK exists -> skip it (line 118) #}
{%- endif -%}

With the bug, the return value is Undefined instead of a constraint name string, so the none == check incorrectly evaluates as "FK doesn't exist" and proceeds to create a duplicate.

Fix — two locations in snowflake__create_constraints.sql:

Cache-hit check (~line 273):

# Before (bug)
{%- for constraint_name, cached_val in lookup_cache.foreign_keys[table_relation].items() -%}
    {%- if dbt_constraints.column_list_matches(cached_val.columns, column_names ) -%}
        {%- do log("Found FK key: " ~ table_relation ~ " " ~ constraint_name ~ " " ~ cached_val.columns ~ " " ~ cached_val.rely, info=false) -%}
        {{ return(cached_val.constraint_name) }}
    {%- endif -%}
{% endfor %}

# After (fix)
{%- for constraint_name, cached_val in lookup_cache.foreign_keys[table_relation].items() -%}
    {%- if dbt_constraints.column_list_matches(cached_val.columns, column_names ) -%}
        {%- do log("Found FK key: " ~ table_relation ~ " " ~ constraint_name ~ " " ~ cached_val.columns ~ " " ~ cached_val.rely, info=false) -%}
        {{ return(constraint_name) }}
    {%- endif -%}
{% endfor %}

Post-DB-lookup check (~line 306):

# Before (bug)
{%- for constraint_name, cached_val in lookup_cache.foreign_keys[table_relation].items() -%}
    {%- if dbt_constraints.column_list_matches(cached_val.columns, column_names ) -%}
        {%- do log("Found FK key: " ~ table_relation ~ " " ~ constraint_name ~ " " ~ cached_val.columns ~ " " ~ cached_val.rely, info=false) -%}
        {{ return(cached_val.constraint_name) }}
    {%- endif -%}
{% endfor %}

# After (fix)
{%- for constraint_name, cached_val in lookup_cache.foreign_keys[table_relation].items() -%}
    {%- if dbt_constraints.column_list_matches(cached_val.columns, column_names ) -%}
        {%- do log("Found FK key: " ~ table_relation ~ " " ~ constraint_name ~ " " ~ cached_val.columns ~ " " ~ cached_val.rely, info=false) -%}
        {{ return(constraint_name) }}
    {%- endif -%}
{% endfor %}

Note that the equivalent snowflake__unique_constraint_exists macro already uses the correct return(constraint_name) pattern in both of its check locations (lines 200 and 253), confirming this is an unintentional inconsistency.

Environment: Snowflake, dbt cloud (Latest non-Fusion), dbt_constraints v1.0.8