auto increment problem.

[realchamp]

Hello. I have a SQL code like this:

 CREATE TABLE IF NOT EXISTS `test`(
         `id` int(10) NOT NULL AUTO_INCREMENT,
         `test2` varchar(15) NOT NULL,
         `test3` varchar(15) NOT NULL,
         PRIMARY KEY(id)
     )

Each time I add a new data it will get id 1 and if I add more it will become id 2. That's correct. But whenever I delete one of them, it doesn't down one down, so if I have added 5 and deleted number 3 and added a new one, that one will become number 6. But there is no id 3, so how can I make it move all the higher numbers one down if a lower number is deleted. Since a list like this is pretty dumb:
1
2
4
5
6
- It should be like this:
1
2
3
4
5
- realchamp.

[Shuja Ali]

What database server are you using? it becomes little difficult to help unless we know which database you are using. Assuming that it is SQL Server, you should be looking at DBCC CHECKIDENT

[realchamp]

What database server are you using? it becomes little difficult to help unless we know which database you are using. Assuming that it is SQL Server, you should be looking at DBCC CHECKIDENT

I am using MySQL.

[Alsvha]

I would like to know why you want to have/need this behavior? Because it isn't the usual need for such an "id" column that it removes "holes" like this.

So my guess is that there's an easier way to handle the actual problem then recreating the id column after each delete.
I don't know how to do it in MySQL, but usually in my experience when there is a need for creative solutions like this, that the actual problem is something else

[jcaccia]

I would like to know why you want to have/need this behavior?

Me too.

realchamp, have you consider the case when this primary key is the foreign key in other tables? (perhaps not now but in the future)

Maintaining the primary keys with no "holes" in this table means that you will have to change the referencing tables as well (not the removed key, obviously because to be able to remove it it should not be referenced by other tables).

Also think of the impact on the performance of the database as well, having to change lots of primary keys (= indexes); with or without tables referecing this key the performance will be deteriorated.

[realchamp]

Me too.

realchamp, have you consider the case when this primary key is the foreign key in other tables? (perhaps not now but in the future)

Maintaining the primary keys with no "holes" in this table means that you will have to change the referencing tables as well (not the removed key, obviously because to be able to remove it it should not be referenced by other tables).

Also think of the impact on the performance of the database as well, having to change lots of primary keys (= indexes); with or without tables referecing this key the performance will be deteriorated.

I've found another solution, else thanks.

[tarik077]

Please let us know what solution you found.

[Mr. Geeker]

I'm not really understanding what you are asking, but if you are trying to resuse auto incroment values, the answer is simply, you can't.

Even if you delete a record you cannot recover that auto incroment value and use it again in another record. The PK only incroments it will never reuse an ID.

[Alsvha]

I'm not really understanding what you are asking, but if you are trying to resuse auto incroment values, the answer is simply, you can't.

Even if you delete a record you cannot recover that auto incroment value and use it again in another record. The PK only incroments it will never reuse an ID.

Well - you can, but it'll require a lot of work which aren't worthwhile.

[Mr. Geeker]

Well - you can, but it'll require a lot of work which aren't worthwhile.

Not sure how you plan on doing that, even if you figure out which numbers arent being used, there is no way to reset the database back and use them. The "auto"incroment values are "auto"matic.

There's no way (at least in MS SQL and probably not in MySQL either) to tell the database to reuse old numbers, and you can't manually insert data into a auto incromenting field.

I don't understand what the big deal is though, if this is a problem the database isn't designed correctly. If your worried you will run out of numbers, then you are using the wrong data type, you need something more custom, or use a Guid.

[Alsvha]

Not sure how you plan on doing that, even if you figure out which numbers arent being used, there is no way to reset the database back and use them. The "auto"incroment values are "auto"matic.

There's no way (at least in MS SQL and probably not in MySQL either) to tell the database to reuse old numbers, and you can't manually insert data into a auto incromenting field.
<snip>

Well - "identity_insert on" and "reseed" should take you a long way.