[Bug] Column constraints (Primary Key, Foreign Keys, Checks, Unique) and Model constraints are not generated

[davikor]

Currently sqlserver_adapter does not generate constraints, except for the not null constraint if it is defined as a column constraint.

The reason, as far as I understand this adapter is:

1. sqlserver_adapter.py implements the render_model_constraintmethod but not the render_column_constraint method. When render_column_constraintis invoked the method implemented from the inherited fabric_adapterclass is invoked which in turn only supports the not null constraint, all other column constraints are ignored.
2. dbt/include/sqlserver/macros/materializations/models//table/table.sq is missing a {{ build_model_constraints(target_relation) }} tag that is present in the fabric template and therefore the render_model_constraint method is never invoked, neither in sqlserver_adapter nor in fabric_adapter.

I did a quick test to confirm that adding these missing parts can fix this issue, however, I do not really understand the functioning and structure of the adapter/jinja templating etc. and therefore cannot provide a fix.

[ericmuijsvanoord]

Constraints will make the run very slow if you do it for all foreign keys for example. Use only when required.

[davikor]

Constraints will make the run very slow if you do it for all foreign keys for example. Use only when required.

the point of this issue is I can add as much foreign keys as I want to my dbt project, it will never get materialized by the sql-adapter.