Fork me on GitHub

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 off of StoreOptions like this:

  1. Computed:
  • single property

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

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

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

    // This creates
    _.Schema.For<User>().UniqueIndex(UniqueIndexType.Computed, x => x.FirstName, x => x.FullName);
});
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);

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

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

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

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

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

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