How to create conditional unique constraint using EF code first approach?

Question

I need to add unique constraint on column allowing null which can be achieved using the following query:

CREATE UNIQUE NONCLUSTERED INDEX idx_yourcolumn_notnull
ON YourTable(yourcolumn)
WHERE yourcolumn IS NOT NULL;

How can I achieve it using Entity Framework code first approach ?

There is `Index` attribute which could be applied to the property of the entity... But unfortunately, there is no `Condition` property for this attribute — Hemid Abbasov, Feb 22 '18 at 14:23
Dupe of https://stackoverflow.com/questions/24361518/ef-6-1-unique-nullable-index — David Browne - Microsoft, Feb 22 '18 at 14:32
Possible duplicate of [EF 6.1 Unique Nullable Index](https://stackoverflow.com/questions/24361518/ef-6-1-unique-nullable-index) — David Browne - Microsoft, Feb 22 '18 at 14:32
@HemidAbbasov yea you are right I was required to add it using migration instead of using index attribute — Simple Code, Feb 22 '18 at 16:06

score 1 · Accepted Answer · answered Feb 22 '18 at 14:34

1

You could use a migration to add the index:

public partial class CreateDatabase : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql("CREATE UNIQUE NONCLUSTERED INDEX ourcolumn_notnull
ON YourTable(yourcolumn)
WHERE yourcolumn IS NOT NULL;");
    }
}

answered Feb 22 '18 at 14:34

Steve Ford

7,093
18
38

Thanks for answering my question. I think also inside down() method I should add "drop index index_name" if I want to revert my migration – Simple Code Feb 22 '18 at 16:04
1

@SimpleCode yes you should add the drop index in the Down method – Steve Ford Feb 22 '18 at 19:38

How to create conditional unique constraint using EF code first approach?

1 Answers1