By OmidTahouri


2010-02-06 18:21:01 8 Comments

I have a MySQL table with a primary key field that has AUTO_INCREMENT on. After reading other posts on here I've noticed people with the same problem and with varied answers. Some recommend not using this feature, others state it can't be 'fixed'.

I have:

table: course
fields: courseID, courseName

Example: number of records in the table: 18. If I delete records 16, 17 and 18 - I would expect the next record entered to have the courseID of 16, however it will be 19 because the last entered courseID was 18.

My SQL knowledge isn't amazing but is there anyway to refresh or update this count with a query (or a setting in the phpMyAdmin interface)?

This table will relate to others in a database.


Given all the advice, I have decided to ignore this 'problem'. I will simply delete and add records whilst letting the auto increment do it's job. I guess it doesn't really matter what the number is since it's only being used as a unique identifier and doesn't have a (as mentioned above) business meaning.

For those who I may have confused with my original post: I do not wish to use this field to know how many records I have. I just wanted the database to look neat and have a bit more consistency.

16 comments

@dani bilel 2017-11-10 00:42:12

here is a function that fix your problem

    public static void fixID(Connection conn, String table) {

    try {
        Statement myStmt = conn.createStatement();
        ResultSet myRs;
        int i = 1, id = 1, n = 0;
        boolean b;
        String sql;

        myRs = myStmt.executeQuery("select max(id) from " + table);
        if (myRs.next()) {
            n = myRs.getInt(1);
        }
        while (i <= n) {
            b = false;
            myRs = null;
            while (!b) {
                myRs = myStmt.executeQuery("select id from " + table + " where id=" + id);
                if (!myRs.next()) {
                    id++;
                } else {
                    b = true;
                }
            }

            sql = "UPDATE " + table + " set id =" + i + " WHERE id=" + id;
            myStmt.execute(sql);
            i++;
            id++;
        }

    } catch (SQLException e) {
        e.printStackTrace();
    }
}

@Joshua Omwoyo 2017-06-19 11:28:35

Its definitely not recommendable. If you have a large database with multiple tables, you may probably have saved a userid as id in table 2. if you rearrange table 1 then probably the intended userid will not end up being the intended table 2 id.

@Aloui Khalil 2017-04-06 10:16:09

You may think about making a trigger after delete so you can update the value of autoincrement and the ID value of all rows that does not look like what you wanted to see.

So you can work with the same table and the auto increment will be fixed automaticaly whenever you delete a row the trigger will fix it.

@Aloui Khalil 2017-04-06 10:43:33

@Vaso Nadiradze 2017-03-17 11:41:11

if($id == 1){ // deleting first row
            mysqli_query($db,"UPDATE employees  SET id=id-1 WHERE id>1");
        }
        else if($id>1 && $id<$num){ // deleting middle row
            mysqli_query($db,"UPDATE employees  SET id=id-1 WHERE id>$id");
        }
        else if($id == $num){ // deleting last row
            mysqli_query($db,"ALTER TABLE employees AUTO_INCREMENT = $num");
        }
        else{
            echo "ERROR";
        }

        mysqli_query($db,"ALTER TABLE employees AUTO_INCREMENT = $num");

@liamvictor 2017-03-17 12:11:07

MySQL is a relational database. When there are several tables there needs to be a relationship defined between the tables. And that is done using ID columns. The problem with this approach is that if you start renumbering the indices of a table there will be knock on effects on all the tables it has a relationship with.

@Geni Jaho 2016-09-02 20:48:27

There is actually a way to fix that. First you delete the auto_incremented primary key column, and then you add it again, like this:

ALTER TABLE table_name DROP column_name;
ALTER TABLE table_name ADD column_name int not null auto_increment primary key first;

@Peter Smartt 2016-01-15 00:19:36

I can think of plenty of scenarios where you might need to do this, particularly during a migration or development process. For instance, I just now had to create a new table by cross-joining two existing tables (as part of a complex set-up process), and then I needed to add a primary key after the event. You can drop the existing primary key column, and then do this.

ALTER TABLE my_table ADD `ID` INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (`ID`);

For a live system, it is not a good idea, and especially if there are other tables with foreign keys pointing to it.

@Claod 2015-02-26 15:02:57

Try :

SET @num := 0;

UPDATE your_table SET id = @num := (@num+1);

ALTER TABLE tableName AUTO_INCREMENT = 1;

That'll reset the autoincremented value, and then count every row while a new value is created for it.

example : before

  • 1 : first value here
  • 2 : second value here
  • X : deleted value
  • 4 : The rest of the table
  • 5 : The rest of the rest..

so the table will display the array : 1,2,4,5

Example : AFTER (if you use this command you will obtain)

  • 1 : first value here
  • 2 : second value here
  • 3 : The rest of the table
  • 4 : the rest of the rest

No trace of the deleted value, and the rest of the incremented continues with this new count.

BUT

  1. If somewhere on your code something use the autoincremented value... maybe this attribution will cause problem.
  2. If you don't use this value in your code everything should be ok.

@Claod 2015-02-26 15:07:25

If you want to preserv old values and set the auto_increment to the max value then you need find the max value: 1. By ordering them, and take the value of the last row/ 2. and then set auto increment to this max value +1 in your code.

@Ariful Islam 2018-05-15 00:27:03

Really appreciating. Its help me a lot to solve my friend's problem. I found this answer after a lot of search on the internet.

@Charles Robertson 2015-04-18 21:31:58

What you are trying to do is very dangerous. Think about this carefully. There is a very good reason for the default behaviour of auto increment.

Consider this:

A record is deleted in one table that has a relationship with another table. The corresponding record in the second table cannot be deleted for auditing reasons. This record becomes orphaned from the first table. If a new record is inserted into the first table, and a sequential primary key is used, this record is now linked to the orphan. Obviously, this is bad. By using an auto incremented PK, an id that has never been used before is always guaranteed. This means that orphans remain orphans, which is correct.

@Dolph 2010-02-06 18:35:36

What you're trying to do sounds dangerous, as that's not the intended use of AUTO_INCREMENT.

If you really want to find the lowest unused key value, don't use AUTO_INCREMENT at all, and manage your keys manually. However, this is NOT a recommended practice.

Take a step back and ask "why you need to recycle key values?" Do unsigned INT (or BIGINT) not provide a large enough key space?

Are you really going to have more than 18,446,744,073,709,551,615 unique records over the course of your application's lifetime?

@OmidTahouri 2010-02-06 19:05:43

You're correct. It's best to ignore it. Considering this is only for an assignment, I won't be going into high numbers and the lifetime is very short.

@PurplePilot 2010-02-06 19:25:48

agree highly dangerous for a number of reasons. One, in a multi user system you may have many, hundreds, thousands of updates per second and trying to rewrite the auto inc could either slow the system down or compromise it. Two another developer would not know you were =doing this perhaps and link records through the id therefor corrupting the system. etc.

@Nils Sens 2016-10-10 21:35:48

Good points. The answer made me think twice. I for one was looking for no gaps in the auto_incr. because I was intending to somehow loop through the ids. But I guess it's better to just loop through the rows :)

@Michael 2017-09-15 18:25:59

So it is simply not possible? There are valid reasons for wanting to do this in a test environment.

@Michel Ayres 2014-05-19 14:39:56

I came here looking for an answer to the Title question "MySQL - Auto Increment after delete" but I could only find an answer for that in the questions

By using something like:

DELETE FROM table;
ALTER TABLE table AUTO_INCREMENT = 1;

Note that Darin Dimitrov's answer explain really well AUTO_INCREMENT and it's usage. Take a look there before doing something you might regret.

PS: The question itself is more "Why you need to recycle key values?" and Dolph's answer cover that.

@monksp 2010-02-06 18:25:18

ALTER TABLE foo AUTO_INCREMENT=1

If you've deleted the most recent entries, that should set it to use the next lowest available one. As in, as long as there's no 19 already, deleting 16-18 will reset the autoincrement to use 16.


EDIT: I missed the bit about phpmyadmin. You can set it there, too. Go to the table screen, and click the operations tab. There's an AUTOINCREMENT field there that you can set to whatever you need manually.

@Mike Sherov 2010-02-06 18:33:05

The OP CAN do this, but he shouldn't. You should really reconsider this advice given what the OP is trying to do.

@monksp 2010-02-06 19:17:28

It's not my place to tell him how to lay out his database, or how to do his business logic. He also mentioned in his post that he's read other posts/pages stating that it's a bad idea, so he knows that it's not a recommended practice, but is going ahead with it anyway.

@Air 2013-07-12 15:10:04

This is the most direct answer currently provided to the asker's only explicit question. There's no "advice" involved.

@ToBe 2014-05-19 14:53:57

Allways a good "practice" though to also advice not to use your solution if you have reasons against it the OP might have missed.

@Charles Robertson 2015-04-18 21:42:24

I think it is our place to advise against bad practice. And believe me, recycling key values is very bad practice. DO NOT DO THIS UNDER ANY CIRCUMSTANCES.

@Jojodmo 2015-07-08 23:03:34

Although this is a bad practice, it is the best answer because it actually answers what the OP asked

@James McCormac 2017-04-19 15:05:29

I agree, so long as you understand the consequences, this is the right answer.

@arefin2k 2012-12-01 18:40:15

I got a very simple but tricky method.

While deleting a row, you can preserve the IDs into another temporary table. After that, when you will insert new data into the main table then you can search and pick IDs from the temporary table. So use a checking here. If the temporary table has no IDs then calculate maximum ID into the main table and set the new ID as: new_ID = old_max_ID+1.

NB: You can not use auto-increment feature here.

@user262976 2010-02-06 18:23:00

you can select the ids like so:

set @rank = 0;
select id, @rank:[email protected]+1 from tbl order by id

the result is a list of ids, and their positions in the sequence.

you can also reset the ids like so:

set @rank = 0;
update tbl a join (select id, @rank:[email protected]+1 as rank from tbl order by id) b
  on a.id = b.id set a.id = b.rank;

you could also just print out the first unused id like so:

select min(id) as next_id from ((select a.id from (select 1 as id) a
  left join tbl b on a.id = b.id where b.id is null) union
  (select min(a.id) + 1 as id from tbl a left join tbl b on a.id+1 = b.id
  where b.id is null)) c;

after each insert, you can reset the auto_increment:

alter table tbl auto_increment = 16

or explicitly set the id value when doing the insert:

insert into tbl values (16, 'something');

typically this isn't necessary, you have count(*) and the ability to create a ranking number in your result sets. a typical ranking might be:

set @rank = 0;
select a.name, a.amount, b.rank from cust a,
  (select amount, @rank:[email protected]+1 as rank from cust order by amount desc) b
  where a.amount = b.amount

customers ranked by amount spent.

@Sarfraz 2010-02-06 18:30:44

You can use your mysql client software/script to specify where the primary key should start from after deleting the required records.

@Darin Dimitrov 2010-02-06 18:28:28

Primary autoincrement keys in database are used to uniquely identify a given row and shouldn't be given any business meaning. So leave the primary key as is and add another column called for example courseOrder. Then when you delete a record from the database you may want to send an additional UPDATE statement in order to decrement the courseOrder column of all rows that have courseOrder greater than the one you are currently deleting.

As a side note you should never modify the value of a primary key in a relational database because there could be other tables that reference it as a foreign key and modifying it might violate referential constraints.

@Mike Sherov 2010-02-06 18:34:42

he's looking to maintain a count, not an ordering. The UPDATE seems unnecessary.

@Darin Dimitrov 2010-02-06 18:39:14

Well if he is looking to maintain a count then there's no need to add additional columns. The simple count aggregate function will do the job.

@Mike Sherov 2010-02-06 18:45:05

right, that's what I was trying to get at.

@OmidTahouri 2010-02-06 18:52:43

Okay, thanks. A lot of answers/comments in such short time! :O I'm trying to take them all in. I will look into the count function :)

@Mike Sherov 2010-02-06 18:30:50

You shouldn't be relying on the AUTO_INCREMENT id to tell you how many records you have in the table. You should be using SELECT COUNT(*) FROM course. ID's are there to uniquely identifiy the course and can be used as references in other tables, so you shouldn't repeat ids and shouldn't be seeking to reset the auto increment field.

@Charles Robertson 2015-04-18 21:51:03

I think he thinks it is some kind of bug. MySQL is 20 years old. This is definitely not an oversight. There is a very good reason why auto increment does not recycle keys. You are totally correct Mike.

Related Questions

Sponsored Content

11 Answered Questions

[SOLVED] How to 'insert if not exists' in MySQL?

12 Answered Questions

[SOLVED] Setting up foreign keys in phpMyAdmin?

  • 2009-01-19 21:48:10
  • Nathan Long
  • 584790 View
  • 325 Score
  • 12 Answer
  • Tags:   mysql phpmyadmin

46 Answered Questions

[SOLVED] How do I import an SQL file using the command line in MySQL?

37 Answered Questions

[SOLVED] Should I use the datetime or timestamp data type in MySQL?

24 Answered Questions

[SOLVED] How to reset AUTO_INCREMENT in MySQL?

19 Answered Questions

11 Answered Questions

[SOLVED] Can I concatenate multiple MySQL rows into one field?

8 Answered Questions

[SOLVED] How to set initial value and auto increment in MySQL?

3 Answered Questions

[SOLVED] Mariadb Auto Increment get reset after deleting all records

1 Answered Questions

[SOLVED] MySQL Auto-Increment Issue with Numbering

Sponsored Content