Added support for MSSQL unique constraint

[mlevit]

MSSQL supports both unique indexes and unique constraints. One of the issues we found was unique index (current implementation) cannot be used with foreign key relationships. We must use unique constraints instead.

This is all based on issue #4825

I thought I'd give it a go and update the way MSSQL handles table.unique. This is my first PR here so please let me know if there's anything missing, which I'm sure there is.

I created a new test which seems to work fine 🤞

[kibertoad]

Can you create a PR for https://github.com/knex/documentation with documentation?
Also TypeScript types need to be updated

[OlivierCavadenti]

You can push tests with add another one in mssql.js file (after "test adding unique key" test) and check the generated SQL.

[mlevit]

@kibertoad could you point me in the right direction? I'm no TS developer, so not really sure what to look at.

[kibertoad]

@mlevit I think somewhere around here:

knex/types/index.d.ts

Line 2078 in 2763e47

unique(columnNames: readonly (string | Raw)[], options?: Readonly<{indexName?: string, storageEngineIndexType?: string, deferrable?: deferrableType}>): TableBuilder;

[mlevit]

@kibertoad @OlivierCavadenti are we happy with the argument name constraint? Is it descriptive enough or should we change it?

[kibertoad]

@mlevit Something more explicitly boolean like isConstraint or useConstraint might be less surprising, but I defer to @OlivierCavadenti here.

[mlevit]

My only issue is the argument is only usable for MSSQL... so not really sure what the best play here is. Would createMssqlConstraint be too verbose? isConstraint is good, but sort of doesn't make sense for other DBs.

[kibertoad]

@mlevit We don't do db-specific naming for db-specific parameters. Note in documentation is sufficient

[mlevit]

Documentation updated knex/documentation#373

[OlivierCavadenti]

@kibertoad @OlivierCavadenti are we happy with the argument name constraint? Is it descriptive enough or should we change it?

useConstraint seems nice in my opinion, more descriptive.

[mlevit]

useConstraint seems nice in my opinion, more descriptive.

Done.

[OlivierCavadenti]

Thanks !