How to reset auto increment in laravel user deletion?

Question

I have been struggling to find out a way to reset the auto increment value in Laravel 4 but it seems that this functionality is not embedded in laravel 4 at least for now. so i did it this way:

$user = User::find($user_id);

                if ($user)  {
                    if ($user->delete()){

                    DB::statement('ALTER TABLE users AUTO_INCREMENT = '.(count(User::all())+1).';');

                    echo json_encode('User Was Deleted Successfully..');
                    }   
            }

each time i delete a user from the database i set the auto increment pointer to the number of all users +1.

if anybody has a better solution inform me please..

Answer 1

Like everyone else replied, there is not really a need to move the counter back when you delete a row. You can however truncate a table which will delete all the table rows and reset the counter.

You cannot truncate a table that has Foreign Key Constraints applied on it (truncate is not the same as delete which simply deletes all the rows while keeping the auto-increment counter.).

Hence, while using foreign key constrains, MySQL might stop you from truncating a table which has foreign key constraints applied to it.

You can perform the following steps to achieve what you want, but beware, there may be a risk to your data integrity. I only use it for my testing purposes.

1. Edit the DatabaseSeeder class (which is available at app/database/seeds/DatabaseSeeder.php) as follows:

   <?php
   class DatabaseSeeder extends Seeder {
       /**
       * Run the database seeds.
       *
       * @return void
       */
       public function run()
       {
           Eloquent::unguard();
   
           // Disable Foreign key check for this connection before running seeders
           DB::statement('SET FOREIGN_KEY_CHECKS=0;');
   
           $this->call('UserTableSeeder');
           // ...
   
           // FOREIGN_KEY_CHECKS is supposed to only apply to a single
           // connection and reset itself but I like to explicitly
           // undo what I've done for clarity
           DB::statement('SET FOREIGN_KEY_CHECKS=1;');
       }
   }
   

2. Now the Table Seeder classes (Example, UserTableSeeder in this case, which should be created at app/database/seeds/UserTableSeeder.php) can call truncate table(s) as follows:

   <?php
   class UserTableSeeder extends Seeder {
   
       public function run()
       {
           // Truncate the table.
           DB::table('users')->truncate();
   
   
           // The auto-increment has been reset.
           // Now we can start adding users.
           User::create(
               array(
                   'email' => '[email protected]',
                   'password' => Hash::make('test')
               )
           );
       }
   }
   

Answer 2

use Illuminate\Support\Facades\DB;

public function refreshDB()
{
    $max = DB::table('users')->max('id') + 1; 
    DB::statement("ALTER TABLE users AUTO_INCREMENT =  $max");
}

// Note: This solution is for resetting the auto_increment of the table without truncating the table itself 

Answer 3

I don't know if it's smart or not, but this will cleanup your table.

public function cleanup($table_name)
{       
    DB::statement("SET @count = 0;");
    DB::statement("UPDATE `$table_name` SET `$table_name`.`id` = @count:= @count + 1;");
    DB::statement("ALTER TABLE `$table_name` AUTO_INCREMENT = 1;");
}

MySQL will set the AUTO_INCREMENT to last+1
If you've set your foreign keys to ON UPDATE CASCADE the children will know about the changes and cascade the update.

This stuff takes server time and gives you little in return. I think that's why you're getting loads of "don't waist your time" responses? For a count you should use ->count() and not the last id.

I also don't know if the statements should be within a transaction to prevent errors when users are added while your statements are running.

Answer 4

If you are using PostgreSQL:

public function resetAutoincrement()
{
    $max = DB::table('users')->max('id') + 1;
    DB::statement('ALTER SEQUENCE users_id_seq RESTART WITH ' . $max);
}