Unique Indexes Edit on GitHub

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

Marten supports both Duplicated Fields for Faster Querying and Calculated Index uniqueness. Using Duplicated Field brings benefits to queries but brings additional complexity, while Computed Index reuses current JSON structure without adding additional db column.

Definining 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);
});

• 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);
});

2. 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);
});

• 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);
});

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; }
}

• 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; }
}

2. Duplicated field:

• single property

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

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

• 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; }
}

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 definining 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.IndexName = "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 = Schema.Indexing.Unique.TenancyScope.PerTenant;

                // Partial index by supplying a condition
                x.Where = "(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;
            });
});

Same can be configured for Duplicated Field:

public class IndexExamples: MartenRegistry
{
    public IndexExamples()
    {
        // Add a gin index to the User document type
        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
        For<User>().Duplicate(x => x.FirstName, pgType: "varchar(50)");

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

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

        // Customize the index on the duplicated field
        // for UserName to be unique
        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
        For<User>().Duplicate(x => x.LastName, configure: idx =>
        {
            idx.SortOrder = SortOrder.Desc;
        });
    }
}

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);
});