Method createIndex

packages/mssql/src/MsSqlSchemaHelper.ts:1018–1045

(index: IndexDef, table: DatabaseTable, createPrimary = false)

  }

  override createIndex(index: IndexDef, table: DatabaseTable, createPrimary = false): string {
    if (index.primary) {
      return '';
    }

    if (index.expression) {
      return index.expression;
    }

    const needsAutoNotNull = index.unique && index.columnNames.some(column => table.getColumn(column)?.nullable);

    if (!needsAutoNotNull) {
      return this.getCreateIndexSQL(table.getShortestName(), index);
    }

    // Strip `index.where` from the base SQL so we can combine it with the auto NOT-NULL guard
    // ourselves, wrapping the user predicate in parens to defuse operator precedence
    // (a bare `a = 1 or b = 2 and [col] is not null` would bind as `a = 1 or (b = 2 and …)`).
    let sql = this.getCreateIndexSQL(table.getShortestName(), { ...index, where: undefined, disabled: false });
    const autoNotNull = index.columnNames.map(c => `${this.quote(c)} is not null`).join(' and ');
    sql += index.where ? ` where (${index.where}) and ${autoNotNull}` : ` where ${autoNotNull}`;

    if (index.disabled) {
      sql += `;\nalter index ${this.quote(index.keyName)} on ${table.getQuotedName()} disable`;
    }

    return sql;
  }

  override dropForeignKey(tableName: string, constraintName: string): string {
    return `alter table ${this.quote(tableName)} drop constraint ${this.quote(constraintName)}`;