[Sam G]

OK say I have 3 rows, with a column called "ID" as follows:

id  other_column
---------------------
1   text
2   text
3   text

If I delete the third row, is there any way I can tell MySQL to use an ID of "3" when a new row is inserted, rather than "4"?

[pat24]

Sam G, on 02 August 2009 - 08:05 AM, said:

OK say I have 3 rows, with a column called "ID" as follows:

 
 id  other_column
 ---------------------
 1   text
 2   text
 3   text
 
 

If I delete the third row, is there any way I can tell MySQL to use an ID of "3" when a new row is inserted, rather than "4"?

I am sure there will be a piece of code somewhere to do this. But why would you want to, if the table has a relationship throughout the database then you would have major problems. It could seriously screw the database.

Pat

[ElanMan]

As Pat said, unless there is very good reason to do this, don't do it.
However, if you want to, use the ALTER TABLE statement like this:

ALTER TABLE tablename DROP id, ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST;

Basically dropping the column and adding it back in the same statement. FIRST places the column first in the table, where it was originally.

[Sam G]

It's just something that I'm working on locally, not something I'd need to do on a live production system.

So there's no way around it without dropping the table first?

[ElanMan]

You're not dropping the table, just the id column

[Sam G]

Sorry I meant column (half asleep today...)

I just found this code snippet on the web (probably should have done that first before asking on here but never mind!):

Quote

Problem One:
If I have entered 10 records, and deleted 9th, 10th records. The next auto increment value will be 11, not 9.

Solution:
Run a query: ALTER TABLE tablename AUTO_INCREMENT = 1

This will reset the next auto increment value to current largest value in the auto increment column + 1. So, the auto increment value of next inserted record will start from 9.

Source: http://www.liewcf.co...reament-number/

Would this not be a better solution?

[ElanMan]

Sorry, I must have also been asleep
I didn't read your post correctly.
I was thinking of re-ordering the auto-incremented id to remove 'gaps' in the whole table, not for the next insert.
Sorry about that

[Sam G]

LOL no worries