Auto Increment after delete in MySQL

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?

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.

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.

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.

you can select the ids like so:

set @rank = 0;
select id, @rank:=@rank+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:=@rank+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:=@rank+1 as rank from cust order by amount desc) b
  where a.amount = b.amount

customers ranked by amount spent.

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

MYSQL Query Auto Increment Solution. It works perfect when you have inserted many records during testing phase of software. Now you want to launch your application live to your client and You want to start auto increment from 1.

To avoid any unwanted problems, for safer side First export .sql file.
Then follow the below steps:

• Step 1) First Create the copy of an existing table MySQL Command to create Copy:

  CREATE TABLE new_Table_Name  SELECT * FROM existing_Table_Name;
  

  The exact copy of a table is created with all rows except Constraints.
  It doesn’t copy constraints like Auto Increment and Primary Key into new_Table_name

• Step 2) Delete All rows If Data is not inserted in testing phase and it is not useful. If Data is important then directly go to Step 3.

  DELETE from new_Table_Name;
  

• Step 3) To Add Constraints, Goto Structure of a table

  • 3A) Add primary key constraint from More option (If You Require).
  • 3B) Add Auto Increment constraint from Change option. For this set Defined value as None.
  • 3C) Delete existing_Table_Name and
  • 3D) rename new_Table_Name to existing_Table_Name.

Now It will work perfectly. The new first record will take first value in Auto Increment column.

Here is a step to solve your problem.

On your .php file, just add this query given below:

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
      die("Connection failed: " . $conn->connect_error);
}

//write the number or id you want to start with the next user in AUTO_INCREMENT    
$sql = "ALTER TABLE `table_name` AUTO_INCREMENT = number";
$conn->query($sql);
?> 

I hope your problem will be solved.

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();
    }
}