Reseeding PK on lookup table fixed DataTables not

Question

EDIT: Okay the error reoccurred again and I know exactly what caused it this time but not sure why. Here is the error screen:

This happened after adding a composite unique index. Before that it was showing the records fine.

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

That's the only change I made to try to enforce uniqueness and it messed up with Datatables display.

---

I have a very simple structure. Two tables. One parent, used as a lookup. One Child.

It's an MVC system with datatables on the front end.

It was working fine - datatables showing the half a dozen or so records in the database. Then I ran a program I have that inserts records from ADODB Excel into the two tables.

Worked fine so far no worries - going from the half a dozen records to maybe 3 figures appropriately in line with the spread sheet records.

Then I noticed that if I happened to run the program accidentally more than once, with the exact same data, that I didn't have anything in the child table to prevent duplicates being created. So I decided to create an index on the child table and make certain fields mandatory (this is using Data Classes and migrations in MVC).

It was after this that I noticed that when I added new records that datatables was no longer able to show any records and showed a Datatables error message (Error 7 I can't now recall).

So I tried to retrace my steps - manually deleting the new records in VS (child and parent) and tried to reverse all the index and field changes to gt it back to where it was working before but despite best efforts I couldnt get datatables to show any records when I tried to add new ones even manually in VS.

Then I noticed that Identity had incremented the PK ID to over 400 in the parent table meaning that I had half a dozen records PK ID - 1 to 6 and then the next couple of records I had entered over PK ID > 400 (the gap being the ID's of the deleted records)

Then I discovered that when I made the foreign key in the child record equal to one of the low values in the parent ID - between 1 and 6 - that it worked - Datatables displayed the all the records including the newly entered record!!

However, when I tried again to make the foreign key in the child record equal to one of the high PK ID numbers > 400 that datatables would not display any records with the same pop up error being displayed.

I started getting desperate hacking about a bit probably more than is advisable and but then after reseeding the Identity counter in the parent ID field and using that newly reset parent ID value (ID: 7) as the foreign key in the child - it worked - the datatables displayed all 7 of the records in the DB - new and old.

I then began rerunning the data transfer program and all the records inserted were displayed appropriately by datatables - no problems!

So my question is what did I do wrong?

I want to be able to use the data transfer routine to add and delete records all the time and dont want to get my knickers in a twist like this again and obviously dont want to have to be reseeding Identity values all the time or at all nor indeed should I have to.

Thank you.