CODEWITHSUNDEEP
mysql
pratim_b
pratim_b

Reputation: 1210

Why mysql autoincrement increments the last used id rather then the last existing id

I am using mysql, and am looking at a strange behavior. Scenario : I have a table having table_id as primary key, which is set to auto-increment.

table_id  more_columns
1         some value
2          others

Now if i delete row 2, and insert one more row, the table_id becomes 3 (Expected is 2)

table_id  more_columns
    1         some value
    3          recent

Why is it so? Here I am loosing some ids (I know they are not important). Please put some lights on this behavior

Upvotes: 0

Views: 192

Answers (3)

Michel Feldheim
Michel Feldheim

Reputation: 18250

The auto_increment value is a counter stored internally for each table. The counter is only increased, never decreased. Every syntactically correct INSERT statement fired against the table increments this counter, even when it is rolled back and also when you define an insert value for the primary key.

Upvotes: 0

Bhushan
Bhushan

Reputation: 6181

In auto-increment field If a row is deleted, the auto_increment column of that row will not be re-assigned.

Please see here for more information.

For reasons why auto-increment doesn't use deleted values you can refer here(mentioned in comments by @AaronBlenkush).

Upvotes: 1

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28413

A MySQL auto_increment column maintains a number internally, and will always increment it, even after deletions. If you need to fill in an empty space, you have to handle it yourself in PHP, rather than use the auto_increment keyword in the table definition.

Rolling back to fill in empty row ids can cause all sorts of difficulty if you have foreign key relationships to maintain, and it really isn't advised.

The auto_increment can be reset using a SQL statement, but this is not advised because it will cause duplicate key errors.

-- Doing this will cause problems!
ALTER table AUTO_INCREMENT=12345;

EDIT To enforce your foreign key relationships as described in the comments, you should add to your table definition:

FOREIGN KEY (friendid) REFERENCES registration_table (id) ON DELETE SET NULL;

Fill in the correct table and column names. Now, when a user is deleted from the registration, their friend association is nulled. If you need to reassociate with a different user, that has to be handled with PHP. mysql_insert_id() is no longer helpful.

If you need to find the highest numbered id still in the database after deletion to associate with friends, use the following.

SELECT MAX(id) FROM registration_table;

After delete write this query

ALTER TABLE tablename AUTO_INCREMENT = 1

Upvotes: 0

Related Questions