Item IDs are skipped in Custom List - SharePoint 2010

Question

How to resolve the following.

1.Created a custom list

2.Added one column with “Enforce unique values”

3.Added the item in list with value '111' for column created in step 2

4.Item is created in the list with ID=1

5.Tried creating another item and gave the value as '111' again for column created in step 2

6.Received the error "This value already exists in the list"

7.Changed the value to '222' and saved the item

8.Item is created in the list with ID=3

Item ID = 2 is skipped. Is there any setting to resolve this ?

Answer 1

In the content database there is a table “AllListsAux” with a column NextAvailableId. That value is incremented for the list before the validation has failed i.e. before the adding of the item has been cancelled.

There is no way to change that value except by running an SQL query but working with the database directly is not recommended. And also that would not be practical in your case because you will need to correct that value after every validation failure.

I am not sure how it will behave if you code the validation in ItemAdding event receiver instead of using ‘Enforce unique values’. If continues IDs is critical to you then you can try that but my guess is that it will have the same behaviour (problem).