Errors re-running pipeline with `foreign_key` constraints

[sheluchin]

Hi all, I'm trying to understand why my run succeeds on the first try and fails on the second. It has something to do with the way I'm defining foreign keys. Here's a minimal repro:

$ tree models/test/
models/test/
├── bar.sql
├── baz.sql
├── foo.sql
└── schema.yml

-- foo.sql
{{ config(materialized='table') }}

select 1 as id, 2 as extra

-- bar.sql
{{ config(materialized='table') }}

select 100 as id, 200 as extra

-- baz.sql
{{ config(materialized='table') }}

select
	1000 as id,
	1 as foo_id,
	100 as bar_id,
	3000 as extra

-- schema.yml
version: 2

models:
  - name: foo
    config:
      contract:
        enforced: true
    columns:
      - name: id
        data_type: int
        data_tests:
          - unique
          - not_null
        constraints:
          - type: not_null
          - type: unique
          - type: primary_key
      - name: extra
        data_type: int
  - name: bar
    config:
      contract:
        enforced: true
    columns:
      - name: id
        data_type: int
        data_tests:
          - unique
          - not_null
        constraints:
          - type: not_null
          - type: unique
          - type: primary_key
      - name: extra
        data_type: int
  - name: baz
    config:
      contract:
        enforced: true
    columns:
      - name: id
        data_type: int
        data_tests:
          - unique
          - not_null
        constraints:
          - type: not_null
          - type: unique
          - type: primary_key
      - name: extra
        data_type: int
      - name: foo_id
        data_type: int
        data_tests:
          - not_null
        constraints:
          - type: not_null
          - type: foreign_key
            columns: [foo_id]
            expression: "foo (id)"
      - name: bar_id
        data_type: int
        data_tests:
          - not_null
        constraints:
          - type: not_null
          - type: foreign_key
            columns: [bar_id]
            expression: "bar (id)"

execution:

+12:37 $ dbt run -s test
16:37:55  Running with dbt=1.8.5
16:37:55  Registered adapter: duckdb=1.8.1
16:37:56  Found 8 models, 35 data tests, 408 macros
16:37:56
16:37:56  Concurrency: 1 threads (target='dev')
16:37:56
16:37:56  1 of 3 START sql table model main.bar .......................................... [RUN]
16:37:56  1 of 3 OK created sql table model main.bar ..................................... [OK in 0.23s]
16:37:56  2 of 3 START sql table model main.foo .......................................... [RUN]
16:37:56  2 of 3 OK created sql table model main.foo ..................................... [OK in 0.08s]
16:37:56  3 of 3 START sql table model main.baz .......................................... [RUN]
16:37:56  3 of 3 OK created sql table model main.baz ..................................... [OK in 0.09s]
16:37:56
16:37:56  Finished running 3 table models in 0 hours 0 minutes and 0.62 seconds (0.62s).
16:37:56
16:37:56  Completed successfully
16:37:56
16:37:56  Done. PASS=3 WARN=0 ERROR=0 SKIP=0 TOTAL=3

+12:37 $ dbt run -s test
16:38:01  Running with dbt=1.8.5
16:38:01  Registered adapter: duckdb=1.8.1
16:38:01  Found 8 models, 35 data tests, 408 macros
16:38:01
16:38:01  Concurrency: 1 threads (target='dev')
16:38:01
16:38:01  1 of 3 START sql table model main.bar .......................................... [RUN]
16:38:02  1 of 3 ERROR creating sql table model main.bar ................................. [ERROR in 0.17s]
16:38:02  2 of 3 START sql table model main.foo .......................................... [RUN]
16:38:02  2 of 3 ERROR creating sql table model main.foo ................................. [ERROR in 0.05s]
16:38:02  3 of 3 SKIP relation main.baz .................................................. [SKIP]
16:38:02
16:38:02  Finished running 3 table models in 0 hours 0 minutes and 0.45 seconds (0.45s).
16:38:02
16:38:02  Completed with 2 errors and 0 warnings:
16:38:02
16:38:02    Runtime Error in model bar (models/test/bar.sql)
  Cannot alter entry "bar" because there are entries that depend on it.
16:38:02
16:38:02    Runtime Error in model foo (models/test/foo.sql)
  Cannot alter entry "foo" because there are entries that depend on it.
16:38:02
16:38:02  Done. PASS=0 WARN=0 ERROR=2 SKIP=1 TOTAL=3

A little bit of discussion on this issue in the Slack thread.

Likely related to https://github.com/duckdb/duckdb/issues/46#issuecomment-1595975462.

[jwills]

cc @matsonj