FK relationship to table already in the database

[MeganBeckett]

hi there

Thanks for this handy package.

I am very new to DBT and currently using it to add some materialized tables to a database which already has tables in it (generated from another process, not dbt).

I want to use dbt_constraints to specify the FK relationships to existing tables in the database. But, these are not created as models with dbt. Is this possible?

At the moment, when I run dbt test and just specify the table name, it says it passes in creating the FK constraints, but these do not show up in the database or schema as actually have been generated.

As an example, I have this:

tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - forecast_period_startdate
            - period_fk
            - facility_fk
            - product_fk
      - dbt_constraints.primary_key:
          column_name: id
          constraint_name: mt_facility_pred_actual_price_pkey
      - dbt_constraints.foreign_key:
          fk_column_name: period_fk
          pk_table_name: period
          pk_column_name: id

So, period is a table already in the database and I want to make the materialized table period_fk column have a Fk constraint to the id of this column. Is this possible?

[sfc-gh-dflippo]

Any table not in your project should be referenced as a source. The dbt Constraints package optionally allows you to create FK, UK, and PK constraints on sources. As described in the readme, you enable this extra feature by adding this to your dbt_project.yml

vars:
  dbt_constraints_sources_enabled: true

It is important to remember that you must have a PK or UK on the parent table. If you have sufficient permissions, you could define those PK/UK on sources in dbt. If you don't you would need to add such constraints outside of dbt before you could create a FK to those tables. Either way, the package will verify whether there is a PK/UK in place before creating a FK.

I have examples using this feature in the integration tests. I recommend reviewing these two YAML that have constraints on sources, between sources, and between models and sources: https://github.com/Snowflake-Labs/dbt_constraints/blob/main/integration_tests/models/sources.yml https://github.com/Snowflake-Labs/dbt_constraints/blob/main/integration_tests/models/schema.yml