Problem With Sql Primary Auto Increment.

[Simcoder]

I have an application that allows new rows to be, added, updated, or deleted from a sql database. The problem Im having is that when I add new rows, the new Primary Key (ID) is not in sequence. For Instance, the database Im currently modifying has 2600 pieces of equipment. When I create a new piece of equipment and add it in to the database. The Id Becomes 2601. So far, so good. Now when I go back and delete that same piece of equipment and then add a new one, The ID becomes 2602. How do I go about making it sequential. When I view the database from Visual Studio, it only shows 2600 pieces of equipment but if I keep adding and deleting rows, the Increment keeps going up, instead of using the next available ID in the sequence. Where is the value being stored at? Ive closed out my program and Visual Studio, but when I come back, and add more equipment the Increment Keeps going up. My database is starting to look like this

...
2599
2600
2610
2615
...

Any help would be much appreciated. Thanks In Advance

-=Simcoder=-

 

[Machaira]

Thats the way it works. When you insert a new record the database does a "SELECT Max(ID)" query, adds one to it, and makes it the new ID. If you want to fill in gaps from deleting records youll have to generate the ID manually, not a process Id recommend. Usually you dont care what the ID is, just that its unique.

 

[bri189a]

That is just the nature of auto increment, if you want something to work as your are suggesting you will have to do the numbering yourself and that can be quite hairy. Bascially your column that keeps track when inserting a record will have to count how many records are currently there, then your delete will have to renumber all the rows above the row that is deleted so the numbers stay sequential. More than likely there is a differant way that you can accomplish what you are trying to do. If you present your problem you might get some good alternatives...in particular how are you using this number in the application that it is important that they be sequential?

For instance, if you are just listing all the items and want them numbered you could use a ListView (in detail display type) and the first column can be a read only column whose value = its item index + 1, and everything will be numbered correctly.

There are several options out there along those lines.

 

[Machaira]

The only way I could think to do this would be to select all the IDs in ID order, loop through them and stop at the first hole and use that number in the insert. It would mean your newest record wouldnt be at the bottom of the list if you sort by ID however. If thats a big deal you could do what bri said, although its a good bit of work.

 

[PlausiblyDamp]

You would also need to consider if the number is used in any relationships - if so what happens if a number is re-used?
How would you handle two simultaneous inserts? Two inserts and one is rolled back?

Unless you have a desparate need to keep them sequential I wouldnt worry about it.

 

[Simcoder]

Thanks for the responses. No there, is not a desperate need to keep them sequential. It actually wont affect anything at all. The only reason I wanted to keep it sequential was because the software that was using the database before me, kept them in sequential order. For instance, since there are 2600 pieces of equipment, you have Primary Keys from 1 to 2600. I thought it might be good practice to keep it consistent but as far as my software goes, it doesnt use the Key for anything nor will it affect any relationships. I just dont understand why its happening. I understand that it will Increment the Max ID value plus one. But the thing I dont get is this, The Last "Visible" Id is 2600, but everytime I add, its going to like 2625 . . . Thats because since I started messing the database, Ive added 25 items, but none of them no longer exists so 2600 is still the MAX ID. Why in the world is it still going to the next number. Well, Its no big deal I guess, but Thanks for trying. I dont think I want to spend too much time on this. I have way may more important things that need to be done. I can always come back if someone has an easy explanation and solution.

-=Simcoder=-

 

[PlausiblyDamp]

In simple terms reusing numbers can cause problems - SQL has no way of knowing if there is a meaning attached to the number (lookup to another table, join etc) and by always using new numbers prevents and problems or confusion caused by reusing existing numbers.
SQL doesnt just pick the MAX(ID) + 1 as the next number as this would cause issues with concurrent pending transactions - each one would need a unique number. In fact inserts that fail (constraint violations, rollback etc) will cause numbers to be skipped in the sequence as a consequence.

 

[Simcoder]

Ahh, Thank You For Enlightening Me. That Is Good To Know!

-=Simcoder=-

 

[cpopham]

Welcome to the wonderful world of relational databases... You can always add a column number that is not keyed and use a loop to renumber each time there is an insert or a delete, but that is more effort than I would care to make. My end users hardly ever see my key fields for the db unless I am using something like serial numbers for my key field.

Chester