Macro drop_fk_constraints() only drops incoming FKs, ignoring outgoing FKs

[jschuba]

The current implementation of the drop_fk_constraints() macro in dbt-sqlserver only identifies and drops foreign keys where the current table is the referenced table. It does not drop foreign keys where the current table is the parent.

SQL Server also requires the foreign keys originating from the current table to be dropped before it can drop or recreate a table.

The proposed change is for the macro drop_fk_constraints.

The join should be expanded to include keys both where the current table is referenced and where it is the parent. Additionally, there should be a check on the schema name. We do not want to drop constraints on tables with the same name, in other schemas.

        inner join sys.tables 
           on sys.foreign_keys.[referenced_object_id] = sys.tables.[object_id] 
           or sys.foreign_keys.[parent_object_id] = sys.tables.[object_id]  -- Proposed Addition
        where sys.tables.[name] = '{{ this.table }}'
          and sys.tables.[schema_id] = SCHEMA_ID('{{ this.schema }}')       -- Proposed Addition