I'm trying to implement an AlternateKey constraint on a table. However I was wondering if it was possible to implement a constraint based on a specific condition.

For example, I have table of records:

As per my constraint, the alternate key should be a pair of the columns Organization ID and Year. However that should only be enforced if the property "IsDeleted" is False. Meaning I want to be able to have more than one row with the same Organization ID and Year pair with IsDeleted as True.

This is what I'm trying to achieve:

In EntityFramework I can easily create the constraint like this:

modelBuilder.Entity<Record>().HasAlternateKey(x => new { x.OrganizationId, x.Year });

What would be the next step to implement this logic?

CodePudding user response：

After you've created a unique index (AlternateForeignKey(...) is short for HasIndex(...).IsUnique) you can then go ahead and create a foreign key that references it something like this:

modelBuilder.Entity<OtherThing>(entity =>
                entity.HasOne(ot => ot.Thing)
                    .WithMany(t => t.OtherThing)
                    .HasForeignKey(ot => new {ot.OrganisationId, ot.Year})
                    .OnDelete(DeleteBehavior.Cascade)
                    .HasConstraintName("OtherThing_FK_Thing");

assuming that you've set up a navigation properties OtherThing.Thing and Thing.OtherThings.

CodePudding user response：

I found out, that the solution to my problem is not to use the ALTERNATE KEY, but instead the UNIQUE INDEX constraint.

The code-first approach is at this point not necessary for me. If I try to create a new entry in the table in C# that conflicts with the constraint, I will handle it in the code eg. by writing catch (SqlException e).

So by executing the following script in SQL I create a conditional constraint for the entities that has IsDeleted set to false, thereby ensuring that only one key pair of OrganizationId and Year can exist that is not deleted:

CREATE UNIQUE INDEX IX_Unique_OrganisationId_Year_Active
    ON Records (OrganizationId, Year) 
    WHERE (IsDeleted = 0);