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;
6/15/2017 2:29:17 AM

Accepted Answer

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

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.

6/17/2017 1:34:09 AM

Popular Answer

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)

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow