Gaspar Bakos <[EMAIL PROTECTED]> wrote on 02/15/2005 04:28:26 PM: > Hi, > > My understanding is that with mysql >= 3.23 versions the last value of > an auto_increm column is stored, thus even if records are deleted, when > a new one is inserted (as NULL), values will not be re-used. > This is a fine attribute, but is there any way to override it? > That is, to bump back the counter by force? > > E.g. I have > test: > 1 > 2 > 3 > 4 > > and then delete "3" and "4": > 1 > 2 > > and when inserting a new record by saying > "insert into test NULL" > , I would like it to become "3". > > Maybe I need a new table creation, and copy of current table in that, etc.? > > Cheers > Gaspar > > --
What you are trying to accomplish is generally considered a "bad practice". Autoincrement numbers should always increase as you add rows to the table (you can reset the "next" value using an ALTER TABLE) and generally they should not be messed with. If you add 4 rows (1,2,3,4) to a new table then delete rows 2 and 3, rows 1 and 4 remain. The next value to be added should be 5 (not 2 as it has been used already) It is generally a BAD THING to change a row's primary key after it has been created. But, in order to maintain your plan of "compact numbering", that would be exactly what you want to happen. You will have to change the 4 to a 2 then issue an ALTER TABLE to reset the autoincrement counter. That's not only messing with any data relationships you once had (what if you had another record in a separate table that once pointed to row 4. What record would it point to now? Row 4 no longer exists. You broke the relationship just to maintain an artificial sequence) but you are working the database WAY too hard to keep those numbers in sequence. I highly suggest that you give up the idea of maintaining sequential primary keys and look into other ways of generating row numbers for your queries. I am not saying it's impossible to do what you ask but I am strongly discouraging it. It just makes so many other things harder if you do what you think you want to do. Most data manipulation libraries have sequence numbers or row numbers or recordset positions as part of the metadata returned with any query's result. You could use one of those values as a sequence number instead of actual table data, for example. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
