Indexes

Create and remove an index

Use an explicit name so the index can be inspected or removed later. Fluent Create.Index(name).OnTable(table).WithColumns(...) names each part explicitly and preserves column order. Append Unique(), Clustered(), IncludeColumns(...) or WithFilter(...). For an existing Index definition, use Create.Index(definition).OnTable(table); fully qualify the model type if System.Index is also in scope.

Database.AddIndex("Users", new DotNetProjects.Migrator.Framework.Index
{
    Name = "IX_Users_Name", KeyColumns = new[] { "Name" }, Unique = false
});

migration.Create.Index("IX_Users_Name").OnTable("Users").WithColumns("Name");

Database.RemoveIndex("Users", "IX_Users_Name");

migration.Delete.Index("IX_Users_Name").FromTable("Users");

Provider options

Index definitions also expose IncludeColumns, FilterItems and Clustered. SQL Server (2008+), PostgreSQL and SQLite support filters on any table column, including columns outside KeyColumns. EqualTo or NotEqualTo with null (or DBNull.Value) becomes IS NULL or IS NOT NULL. GetIndexes(table), also available as Schema.Table(table).Indexes() in fluent migrations, reads back the keys, included columns, flags and supported FilterItems. Filters preserve null checks and escaped string values.

UnsupportedFilterBehavior defaults to UnsupportedIndexFilterBehavior.Throw. Set it to Ignore, or append OnUnsupportedFilter(UnsupportedIndexFilterBehavior.Ignore) in fluent code, to create an unfiltered index on a provider that cannot apply the filters. For unique indexes this enforces uniqueness across all rows. This option only affects unsupported filters; it does not suppress other invalid options or execution failures.

Oracle supports filtered unique indexes through functional expressions: every key column uses CASE WHEN with the complete filter predicate, so non-matching rows produce all-NULL keys and are omitted. Filters may reference non-key columns. Oracle retains its limited non-unique, key-column expression emulation and cannot read functional expressions back as FilterItems. Oracle rejects included and clustered index requests; SQLite reconstruction rejects existing index SQL with explicit COLLATE clauses.

The fallback policy is an authoring option and is not stored in database metadata. Preview handles simple indexes and rejects filtered indexes even in Ignore mode.

Unique index or unique constraint?

Use UniqueConstraint for a table-level invariant and an Index with Unique for an index definition. Do not infer ownership from a generated name. SQLite RemoveAllIndexes preserves declared table UNIQUE constraints; remove those through the constraint APIs. Check query plans and data cardinality when choosing index keys.