Adding a unique index on EF Core mapping does not seem to work

Question

we're using EF Core in our ASP.NET core API solution. There is a transactions table in the database and there is a unique index that contains two columns. So there should be no records in this table with the same values for these columns.

In our EF mapping we have this

        builder.HasIndex(e => new { e.RsaSessionId, e.SessionId }).IsUnique(true).HasName("IX_Transactions");

        builder.Property(e => e.RsaSessionId)
            .HasColumnName(nameof(Transaction.RsaSessionId))
            .IsRequired();

        builder.Property(e => e.SessionId)
            .HasColumnName(nameof(Transaction.SessionId))
            .IsRequired()
            .HasColumnType("uniqueidentifier");

But in our integration tests where we use the in memory database provider, there will be no error raised when two identical transaction objects are added in the Transactions DbSet of the DbContext. Shouldn't this code raise an error, since we've specified that there is a unique key that includes these two columns?

var rsaSessionID=1;
Guid issuerSessionId=Guid.NewGuid();

//create two transactions with the same values for the two fields in the unique index
    var transaction = new Transaction(rsaSessionID, issuerSessionId, ... other fields ... );
    var transaction = new Transaction(rsaSessionID, issuerSessionId, ... other fields ... );
    this.fixture.Context.Transactions.Add(transaction);
this.fixture.Context.Transactions.Add(transaction2);
this.fixture.Context.SaveChanges();

Any help is greatly appreciated.

Answer 1

InMemory is not a relational database.

InMemory will allow you to save data that would violate referential integrity constraints in a relational database.

If you want to test against something that behaves more like a true relational database, then consider using SQLite in-memory mode.

Reference: InMemory is not a relational database