Adding unique index in Entity Framework prevents D

Question

It's a very simple structure - parent child with foreign key 1 to many.

Was working fine but I wanted to enforce uniqueness by adding a composite unique index as so:

CreateIndex("dbo.Fixtures", new[] { "MarketId", "BookName", "CoupName" }, unique: true, name: "IX_UniqueFixture");

After this when I entered new records it produced the error:

I then deleted the newly added index and deleted the records added after the index was added and it worked displaying the records that predated the addition of the index.

However, when I tried again to update the database with new records it reverted to the same error:

The records shown underneath the error message are the records that existed prior to the addition of the index mentioned earlier.

Now after reseeding the parent lookup table Id column as so - it works

USE [aspnet-Arb-20160906102730] 

GO
DBCC CHECKIDENT ('ExchangeTypes', RESEED, 7) GO

and it once again allows me to add new records

So in short I managed to recover from the messing up of the datatables by the addition of the unique composite index so that it allows the displaying of records.

However, it leaves me puzzled as to why this is occurring. And I still want to be able to enforce uniqueness. Maybe I will try to concatenate 3 fields into one field and see if I can get away with enforcing uniqueness with an index on a single field rather than several fields and see if that doesn't interfere with datatables display.

EDIT: Tried it with Index on single field unique and it still comes up with the same error. Funny how applying a unique index on the parent lookup field doesn't prevent datatables from working but attempting the same on the child table does.

Answer 1

EDIT 3 LOOKS LIKE COULD BE SOLVED WITH WORKAROUND

I never knew this could be so stupidly unpredictable like this thinking it would just take care of itself with the auto increment and never imagined it would have such a prohibitive effect on Datatables rows display but any how it looks like this could work on initial testing unless it somehow breaks as I've only tested it once with the deletion and subsequent addition of records. Fingers crossed it is a suitable work around anyway unless someone can get to the bottom of it and offer a better solution.

Looks like I am going to have to run this piece of code every time I perform a deletion - resetting the maximum identity value to be equal to the current identity value in the PK.ID column after every deletion as shown below:

DECLARE @maxIdentityETValue INT
DECLARE @maxIdentityFValue INT

SET @maxIdentityETValue = (SELECT MAX(Id) FROM ExchangeTypes)
DBCC CHECKIDENT('ExchangeTypes', RESEED, @maxIdentityETValue)

SET @maxIdentityFValue = (SELECT MAX(Id) FROM Fixtures)
DBCC CHECKIDENT('Fixtures', RESEED, @maxIdentityFValue)

DBCC CHECKIDENT ('ExchangeTypes', NORESEED) 
DBCC CHECKIDENT ('Fixtures', NORESEED)

---

EDIT 2 NOT SOLVED.

Tried deleting and then adding records and further to the deleting it would not allow any subsequent additions to be displayed anymore. :(

See the step up in Identity generated ID values - that's what's causing the problem - well at least if I dont reseed those numbers after deletions and before subsequently adding records then Datatables displays the new records. But if I dont reseed them further to deleting records and before subsequently adding records it is still preventing datatables from displaying the records and shows the above error.

Maddening - someone please help me!

---

EDIT 1: Solved. Well kind of.

When adding an index, well a unique index at least, it is necessary to reseed both parent and child PK Identity generated ID values in order that the datatables work - displaying the records error free.

Don't ask me why but it just works.

Further to adding my unique enforcing constraints in the indexes, parent and child respectively, I after reseeding both identity columns as descibed, I am now able to add new records to the database and datatables displays them fine.