I have a test table with 3 collumns named RecordID Identity(1,1) (int), ParentID(references RecordID) (int, null), and Data (just to insert to, it is a char datatype).
The ParentID column references the Record ID column, and can be null (as in a table of employees, some of which are supervised by others, but the supervisors would have that column as a null).
The problem I am having is this:
If I insert a few records, the first with ParentID as Null, the rest referencing the first or any row before the new one, they go in fine, with RecordID incrementing by one as it is supposed to.
Then I go to insert a row with ParentID referencing a row that does not exist (like say 1000000). As you would expect, this insert fails. If I do it say 20 times, it will fail 20 times.
Now, I go to insert a column with ParentID referencing a valid column, and the next record is inserted, but the RecordID column has skiped a number for each failed row...so If i tried to insert 20 rows and they failed, my next row will have an ID of PreviousRow+20.
Somehow the failed inserts are causing the ID column to increment.
Does anyone have any input on this? Is it a glitch or am I missing something here? I have never seen this before, could it have to do with the table referencing itself?