CODEWITHSUNDEEP
phpmysqlsqlphpmyadmin
tect
tect

Reputation: 93

Deleting rows not returning to original numbers

Just working with a database and some tests were done recently which checked the integrity of the setup.

As a result, a lot of test entries were added which were then deleted. However, when new entries are added, the ID number value continues from after the entries added.

What I want: ID increases by one from where it left off before the additional rows were added: 4203, 4204, 4205, 4206 etc.

What is happening: ID increases by one from after the additional rows ID: 4203, 4204, 6207, 6208 6209 etc.

Not sure where to fix this...whether in phpmyadmin or in the PHP code. Any help would be appreciated. Thanks!

Upvotes: 0

Views: 215

Answers (4)

spencer7593
spencer7593

Reputation: 108380

There should be no need to "reset" the id values; I concur with the other comments concerning this issue.

The behavior you observe with AUTO_INCREMENT is by design; it is described in the MySQL documentation.

With all that said, I will describe an approach you can use to change the id values of those rows "downwards", and make them all contiguous:

As a "stepping stone" first step, we will create a query that gets a list of the id values that we need changed, along with a proposed new id value we are going to change it to. This query makes use of a MySQL user variable.

Assuming that 4203 is the id value you want to leave as is, and you want the next higher id value to be reset to 4204, the next higher id to be reset to 4205, etc.

SELECT s.id
     , @i := @i + 1 AS new_id
  FROM mytable s
  JOIN (SELECT @i := 4203) i
 WHERE s.id > 4203
 ORDER BY s.id

(Note: the constant value 4203 appears twice in the query above.)

Once we're satisfied that this query is working, and returning the old and new id values, we can use this query as an inline view (MySQL calls it a derived table), in a multi-table UPDATE statement. We just wrap that query in a set of parentheses, and give assign it an alias, so we can reference it like a regular table. (In an inline view, MySQL actually materializes the resultset returned by the query into a MyISAM table, which probably explains why MySQL refers to it as a "derived table".)

Here's an example UPDATE statement that references the derived table:

UPDATE ( SELECT s.id
              , @i := @i + 1 AS new_id
           FROM mytable s
           JOIN (SELECT @i := 4203) i
          WHERE s.id > 4203
          ORDER BY s.id 
       ) n
  JOIN mytable t
    ON t.id = n.id
   SET t.id = n.new_id
 ORDER BY t.id

Note that the old id value from the inline view is matched to the id value in the existing table (the ON clause), and the "new_id" value generated by the inline view is assigned to the id column (the SET clause.)

Once the id values are assigned, we can reset the AUTO_INCREMENT value on the table:

ALTER TABLE mytable AUTO_INCREMENT = 1;

NOTE: this is just an example, and is provided with the caveat that this should not be necessary to reassign id values. Ideally, primary key values should be IMMUTABLE i.e. they should not change once they have been assigned.

Upvotes: 0

amaster
amaster

Reputation: 2163

I have ran into this before and I solve it easily with phpMyAdmin. Select the database, select the table, open the operations tab, and in the Table Options set the AUTO_INCREMENT to 1 then click GO. This will force mysql to look for the last auto incremented value and then set it to the value directly after that. I do this on a manually basis that way I know that when a row is skipped that it was not from testing but a deletion because when I test and delete the rows I fix the AI value.

Upvotes: 2

Guy Incognito
Guy Incognito

Reputation: 500

You could drop the primary key then recreate it, but this would reassign all the existing primary keys so could cause issues with relationships (although if you don't have any gaps in your primary key you may get away with it).

I would however say that you should accept (and your app should reflect) the possibility of missing IDs. For example in a web app if someone links to a missing ID you would want a 404 returned not a different record.

Upvotes: 0

Don Dickinson
Don Dickinson

Reputation: 6248

I don't think there's a way to do this with an auto-incrementing ID key. You could probably do it by assigning the ID to (select max(id) + 1 from the_table)

Upvotes: 1

Related Questions