knex/knex · error · Error

mssql cannot create constraint with predicate

Error message

mssql cannot create constraint with predicate

What it means

mssql's `unique()` in the table compiler throws when you request BOTH `useConstraint: true` AND a `predicate` in the options object. SQL Server unique constraints cannot carry a `WHERE` predicate (only filtered unique *indexes* can), so Knex rejects the contradictory combination at compile time.

Source

Thrown at lib/dialects/mssql/schema/mssql-tablecompiler.js:299

   *
   * @param {string | string[]} columns
   * @param {string | {indexName: undefined | string, deferrable?: 'not deferrable'|'deferred'|'immediate', useConstraint?: true|false, predicate?: QueryBuilder }} indexName
   */
  unique(columns, indexName) {
    /** @type {string | undefined} */
    let deferrable;
    let useConstraint = false;
    let predicate;
    if (isObject(indexName)) {
      ({ indexName, deferrable, useConstraint, predicate } = indexName);
    }
    if (deferrable && deferrable !== 'not deferrable') {
      this.client.logger.warn(
        `mssql: unique index [${indexName}] will not be deferrable ${deferrable} because mssql does not support deferred constraints.`
      );
    }
    if (useConstraint && predicate) {
      throw new Error('mssql cannot create constraint with predicate');
    }
    indexName = indexName
      ? this.formatter.wrap(indexName)
      : this._indexCommand('unique', this.tableNameRaw, columns);

    if (!Array.isArray(columns)) {
      columns = [columns];
    }

    if (useConstraint) {
      // mssql supports unique indexes and unique constraints.
      // unique indexes cannot be used with foreign key relationships hence unique constraints are used instead.
      this.pushQuery(
        `ALTER TABLE ${this.tableName()} ADD CONSTRAINT ${indexName} UNIQUE (${this.formatter.columnize(
          columns
        )})`
      );
    } else {

View on GitHub (pinned to e25d54bcb7)

Solutions

1. For a filtered/partial unique rule on mssql, use a unique *index* with a WHERE filter: drop `useConstraint` and keep `predicate` (Knex emits `CREATE UNIQUE INDEX ... WHERE ...`).
2. If you specifically need a constraint (e.g. for FK relationships), remove the `predicate` — constraints cannot be filtered on SQL Server.
3. Express soft-delete uniqueness via a computed column (e.g. a `NULL`-able filtered unique index on a computed expression).
4. Branch migration DDL by dialect for partial-unique rules.

Example fix

// before
knex.schema.alterTable('users', t => t.unique('email', { useConstraint: true, predicate: knex.raw('WHERE deleted_at IS NULL') }));
// after (filtered unique index, not a constraint)
knex.schema.alterTable('users', t => t.unique('email', { indexName: 'uq_users_email_active', predicate: knex.raw('WHERE deleted_at IS NULL') }));

function buildUnique(t, columns, opts = {}) {
  if (opts.useConstraint && opts.predicate) {
    throw new Error('mssql cannot create a constraint with a predicate; use a unique index instead');
  }
  return t.unique(columns, opts);
}

function isValidMssqlUniqueOptions(o) { return !(o && o.useConstraint && o.predicate); }

When it happens

Trigger: Calling `.unique('col', { useConstraint: true, predicate: knex.raw('...') })` or the option-object form `{ indexName, useConstraint: true, predicate: ... }`; migrating a postgres partial unique constraint to mssql verbatim.

Common situations: Partial unique constraints (e.g. 'unique email where active = 1') ported from postgres to SQL Server; generic migration generators that always set `useConstraint` plus a predicate; conditional uniqueness needed for soft-delete patterns.

Related errors

• rename column of views is not supported by this dialect.
• change default values of views is not supported by this dial
• The query is empty
• The binding value ${binding} must be a decimal number.
• Bigint must be safe integer or must be passed as string, saw

AI-assisted analysis of knex/knex@e25d54bcb7 (2026-08-03). Data as JSON: /data/errors/e3583ab12f6364ee.json. Report an issue: GitHub.