How to reset auto increment in laravel user deletion?
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
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.
Edit the
DatabaseSeeder
class (which is available atapp/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;'); } }
Now the Table Seeder classes (Example,
UserTableSeeder
in this case, which should be created atapp/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') ) ); } }
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.