Failed to create PK constraint if a table already has PK constraint on another column or columns

[kokorin]

Some of our models have incremental materialization. This model contains some history we want to keep, so we disabled full_refresh for it.

We wanted to change PK column after adding synthetic (generated) column.

dbt_constraints doesn't check if PK constraint already exists and should be dropped.

[sfc-gh-dflippo]

Even with all the new features just released in version 1.0.0, this is still true. There isn't any logic to drop any existing constraints if they have changed. I have added this as a limitation in the README.md.

[kokorin]

We ended up with specific DBT pre-hook to drop constraints in Snowflake:

{% set relation = load_relation( this ) %}
{# prefilled lookup_cache is required by dbt_contraints #}
{%- set lookup_cache = {
    'table_columns': { },
    'table_privileges': { },
    'unique_keys': { },
    'not_null_col': { },
    'foreign_keys': { } } -%}
{% if relation is not none and dbt_constraints.unique_constraint_exists(relation, ['previous_unique_column'], lookup_cache) %}
  alter table {{ this }} drop primary key;
{% else %}
  select 1
{% endif %}