CODEWITHSUNDEEP
sqlsql-serveref-code-firstentity-framework-core
Heinrich
Heinrich

Reputation: 2234

EF Core Check constraint

Is it possible to add a check constraint to a table via EF core.

I have been looking around the only question I found is for Ef 7 with an anwesr over a year ago. So I am just wandering if anything has changed. Which gave the answer as below:

migrationBuilder.Sql("ADD CONSTRAINT CK_SomeTable_SomeColumn CHECK (SomeColumn >= X);");

Is there another way to do this? I want to apply a unique constraint as below:

CREATE UNIQUE INDEX unq_t_parent_child ON t(parent, child)
WHERE isdeleted = 0;

Upvotes: 3

Views: 5648

Answers (3)

JCisar
JCisar

Reputation: 2674

For newcomers to this in 2021+. EF Core 3+ has support for check constraints. See: https://stackoverflow.com/a/60199728/1179562

Upvotes: 3

Smit
Smit

Reputation: 2459

Assuming your entity is defined as 

public class Entity
{
    public int Id { get; set; }
    public int Parent { get; set; }
    public int Child { get; set; }
}

Following fluent API code will create index as you desire:

modelBuilder.Entity<Entity>().HasIndex(p => new {p.Parent, p.Child})
        .HasFilter("isdeleted = 0")
        .HasName("unq_t_parent_child");

SQL generated

CREATE INDEX [unq_t_parent_child] ON [Entity] ([Parent], [Child]) WHERE isdeleted = 0;

HasIndex defines index over properties in the table Entity

HasFilter allows you to set a filter for your index. This value is sql so you need to make sure you are writing correct sql syntax.

HasName configures the name of the index.

(If you map Entity to table t & the properties to their column names, migrations will create exactly same sql as you want.)

Also a check constraint is different from unique index. If you are looking to add check constraint then you need to use migrationBuilder.Sql in your migration file.

Upvotes: 4

sepupic
sepupic

Reputation: 8687

Unique constraint is not check constraint, anyway, you can create it this way:

alter table dbo.t
ADD CONSTRAINT UQ_parent_child UNIQUE (parent, child)

Upvotes: 2

Related Questions