Unique Indexes

Unique Indexes are used to enforce uniqueness of property value. They can be combined to handle also uniqueness of multiple properties.

Marten supports both duplicate fields and calculated indexes uniqueness. Using Duplicated Field brings benefits to queries but brings additional complexity, while Computed Index reuses current JSON structure without adding additional db column.

Defining Unique Index through Store options

Unique Indexes can be created using the fluent interface of StoreOptions like this:

1. Computed:

• single property

var store = DocumentStore.For(_ =>
{
    _.Connection(ConnectionSource.ConnectionString);
    _.DatabaseSchemaName = "unique_text";

    // This creates
    _.Schema.For<User>().UniqueIndex(UniqueIndexType.Computed, x => x.UserName);
});

^{snippet source | anchor}

• multiple properties

var store = DocumentStore.For(_ =>
{
    _.Connection(ConnectionSource.ConnectionString);
    _.DatabaseSchemaName = "unique_text";

    // This creates
    _.Schema.For<User>().UniqueIndex(UniqueIndexType.Computed, x => x.FirstName, x => x.FullName);
});

^{snippet source | anchor}

INFO

If you don't specify first parameter (index type) - by default it will be created as computed index.

1. Duplicated field:

• single property

var store = DocumentStore.For(_ =>
{
    _.Connection(ConnectionSource.ConnectionString);
    _.DatabaseSchemaName = "unique_text";

    // This creates
    _.Schema.For<User>().UniqueIndex(UniqueIndexType.DuplicatedField, x => x.UserName);
});

^{snippet source | anchor}

• multiple properties

var store = DocumentStore.For(_ =>
{
    _.Connection(ConnectionSource.ConnectionString);
    _.DatabaseSchemaName = "unique_text";

    // This creates
    _.Schema.For<User>().UniqueIndex(UniqueIndexType.DuplicatedField, x => x.FirstName, x => x.FullName);
});

^{snippet source | anchor}

Defining Unique Index through Attribute

Unique Indexes can be created using the [UniqueIndex] attribute like this:

1. Computed:

• single property

public class Account
{
    public Guid Id { get; set; }

    [UniqueIndex(IndexType = UniqueIndexType.Computed)]
    public string Number { get; set; }
}

^{snippet source | anchor}

• multiple properties

public class Address
{
    private const string UniqueIndexName = "sample_uidx_person";

    public Guid Id { get; set; }

    [UniqueIndex(IndexType = UniqueIndexType.Computed, IndexName = UniqueIndexName)]
    public string Street { get; set; }

    [UniqueIndex(IndexType = UniqueIndexType.Computed, IndexName = UniqueIndexName)]
    public string Number { get; set; }
}

^{snippet source | anchor}

INFO

If you don't specify IndexType parameter - by default it will be created as computed index.

1. Duplicated field:

• single property

public class Client
{
    public Guid Id { get; set; }

    [UniqueIndex(IndexType = UniqueIndexType.DuplicatedField)]
    public string Name { get; set; }
}

^{snippet source | anchor}

• multiple properties

public class Person
{
    private const string UniqueIndexName = "sample_uidx_person";

    public Guid Id { get; set; }

    [UniqueIndex(IndexType = UniqueIndexType.DuplicatedField, IndexName = UniqueIndexName)]
    public string FirstName { get; set; }

    [UniqueIndex(IndexType = UniqueIndexType.DuplicatedField, IndexName = UniqueIndexName)]
    public string SecondName { get; set; }
}

^{snippet source | anchor}

INFO

To group multiple properties into single index you need to specify the same values in IndexName parameters.

Defining Unique Index through Index customization

You have some ability to extend to Computed Index definition to be unique index by passing a second Lambda Action into the Index() method and defining IsUnique property as true as shown below:

var store = DocumentStore.For(_ =>
{
    _.Connection(ConnectionSource.ConnectionString);

    // The second, optional argument to Index()
    // allows you to customize the calculated index
    _.Schema.For<Target>().Index(x => x.Number, x =>
    {
        // Change the index method to "brin"
        x.Method = IndexMethod.brin;

        // Force the index to be generated with casing rules
        x.Casing = ComputedIndex.Casings.Lower;

        // Override the index name if you want
        x.Name = "mt_my_name";

        // Toggle whether or not the index is concurrent
        // Default is false
        x.IsConcurrent = true;

        // Toggle whether or not the index is a UNIQUE
        // index
        x.IsUnique = true;

        // Toggle whether index value will be constrained unique in scope of whole document table (Global)
        // or in a scope of a single tenant (PerTenant)
        // Default is Global
        x.TenancyScope = Marten.Schema.Indexing.Unique.TenancyScope.PerTenant;

        // Partial index by supplying a condition
        x.Predicate = "(data ->> 'Number')::int > 10";
    });

    // For B-tree indexes, it's also possible to change
    // the sort order from the default of "ascending"
    _.Schema.For<User>().Index(x => x.LastName, x =>
    {
        // Change the index method to "brin"
        x.SortOrder = SortOrder.Desc;
    });
});

^{snippet source | anchor}

Same can be configured for Duplicated Field:

var store = DocumentStore.For(options =>
{
    // Add a gin index to the User document type
    options.Schema.For<User>().GinIndexJsonData();

    // Adds a basic btree index to the duplicated
    // field for this property that also overrides
    // the Postgresql database type for the column
    options.Schema.For<User>().Duplicate(x => x.FirstName, pgType: "varchar(50)");

    // Defining a duplicate column with not null constraint
    options.Schema.For<User>().Duplicate(x => x.Department, pgType: "varchar(50)", notNull: true);

    // Customize the index on the duplicated field
    // for FirstName
    options.Schema.For<User>().Duplicate(x => x.FirstName, configure: idx =>
    {
        idx.Name = "idx_special";
        idx.Method = IndexMethod.hash;
    });

    // Customize the index on the duplicated field
    // for UserName to be unique
    options.Schema.For<User>().Duplicate(x => x.UserName, configure: idx =>
    {
        idx.IsUnique = true;
    });

    // Customize the index on the duplicated field
    // for LastName to be in descending order
    options.Schema.For<User>().Duplicate(x => x.LastName, configure: idx =>
    {
        idx.SortOrder = SortOrder.Desc;
    });
});

^{snippet source | anchor}

Unique Index per Tenant

For tables which have been configured for tenancy, index definitions may also be scoped per tenant.

var store = DocumentStore.For(_ =>
{
    _.Connection(ConnectionSource.ConnectionString);
    _.DatabaseSchemaName = "unique_text";

    // This creates a duplicated field unique index on firstname, lastname and tenant_id
    _.Schema.For<User>().MultiTenanted().UniqueIndex(UniqueIndexType.DuplicatedField, "index_name", TenancyScope.PerTenant, x => x.FirstName, x => x.LastName);

    // This creates a computed unique index on client name and tenant_id
    _.Schema.For<Client>().MultiTenanted().UniqueIndex(UniqueIndexType.Computed, "index_name", TenancyScope.PerTenant, x => x.Name);
});

^{snippet source | anchor}