SQL Auto Increment Problem?

Question

Hi, everyone. I have a simple problem I'm dealing with in localhost, using MySQL. I have a column called ID and it's set to auto increment every time there's a new row added. The problem is I've been deleting and creating new rows and now when I add a new row, the ID is incremented from the last row, which was deleted.

Basically, I had 4 fours, I deleted the forth row, then added a new row, and now the ID for that new row is 5. I want it to be 4 without having to edit the row manually. How is this problem fixed?

Thanks!

Answer 1

There is actually a pretty good reason why it skips. Your ID sounds like it is the primary key for the entry, therefore any reference anywhere in the schema to that table, will go by its primary key. By forcing you to skip one that's already been used, you don't have an incident where you delete a user...and then a new user joins and now everything the first user did is now tied to the first user. This also works with threads and posts, forums and threads, etc. As such, when denoted as a Primary Key, it will always leave that missing gap when you delete an entry. I do want to know, however, why it actually matters? If it matters enough you can edit it to be the case, as you shouldn't have to do it often. If you do have a specific reason NO gap can exist regardless of you deleting the last one..., why is it auto-incrementing instead of you just having the code retrieve the last id and +1?

Answer 2

>> How is this problem fixed?



It isn't, and don't even try. That is the nature of auto-incrementing columns in just about every database out there. The *only* thing that such a column will typically guarantee you is that every value will be unique, and greater than previous values in the column. It *NEVER*, in any database I have seen, guarantee's no gaps.



That is not a *BAD* thing. The single most important thing an auto-increment column gives you is a *ROCK SOLID* guarantee that the value will be unique. Count on that, but nothing else.



The question I would ask you is: Why do you need this? Why do you need the database to go backfill your ID column with deleted ID values? What problem are you *REALLY* trying to solve? I am 110% certain that there is a better way to do whatever you are trying to do here.



Tell us what you are really trying to accomplish, and the answer will probably be easy.

Answer 3

That's generally how auto-increment works. You can manually change it as long as you don't have other external resources relying on that data. What you do is DROP the field that you're auto-incrementing, and then ALTER the table to ADD the field again with the exact same attributes. All the existing rows will be renumbered correctly.



Be careful though because this WILL delete all the data in that field.

Answer 4

Get a book on RDBMS and learn why an auto-increment can NEVER be a

relational key. Learn why a table name should be a collective or

plural name (unless there is only one row in the table). Learn way

"id" is too vague to be data element name. Learn why camelCase is a

***** to read --hint: where does your eye jump when you see an

Uppercase letter?



I know you want to have a "Magical Universal Key" that will solve all

your design problems, without having to really think or learn anything.

What is your **real key** in your **real** problem?



Going to a Newsgroup to get what usually takes a few YEARS of college

and experience does not work.

Answer 5

Gaps are going to occur with an auto-increment field due to deletions over time...that's just fine and there's no reason to mess with it. If you want an identifier that doesn't leave gaps, you could do something like this:



Have a table populated with all the sequential identifiers you ever expect to use called something like

Identifier (identID)



Define a UDF to execute the following (this assumes just 1 primary table that uses it) and return the result

SELECT MIN(identID) FROM Identifier WHERE identID NOT IN

(SELECT otherIdentifer FROM primaryTable)



Then just execute the UDF to get the first 'available' identifier whenever you do an insert to primaryTable.

Answer 6

You can't. The database has a counter for that auto increment field. The counter is what's used to determine the next number, not the number of the last row.

Answer 7

To create a table with an motor vehicle-increment column you are able to desire to apply some thing like CREATE table stuff(identity BIGINT AUTO_INCREMENT, call VARCHAR(40), handle VARCHAR(255), top DECIMAL(4.2), huge-unfold KEY(identity)); and then whilst doing an INSERT question, purely insert NULL into the auto_increment column: INSERT INTO stuff VALUES(NULL, "H. Brown","a million Tooting extreme highway, London, SW17 0RG" , a million.80 5); or (greater advantageous, considering which you need to to alter the table shape in destiny and this way you will not cock your modern scripts up) purely insert into named fields in basic terms: INSERT INTO stuff(call,handle,top) VALUES("Henry Brown", "a million, Tooting extreme highway, London, SW17 0RG", a million.80 5);