Set Auto Increment field start form 1000 in migrat

Question

I need to start my ids from 1000 in user table, how could I create migration for this.

My current migration is:

public function up()
{
    Schema::create('users', function (Blueprint $table) {
        $table->increments('id'); // how can I start this from 1000
        $table->integer('qualification_id')->nullable();
        $table->integer('experience_id')->nullable();
    });
}

Answer 1

Migration to create table and set its auto-increment value as of Laravel 5.5

public function up()
{
    Schema::create('users', function (Blueprint $table) {
        $table->increments('id');
        $table->integer('qualification_id')->nullable();
        $table->integer('experience_id')->nullable();
    });

    // Here's the magic
    \DB::statement('ALTER TABLE table_name AUTO_INCREMENT = 1000;');
}

DB::statement() can be used to execute any single SQL statement you need.

Answer 2

It should be like this(not tested).

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;

class MyTableMigration extends Migration {

     /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        $statement = "ALTER TABLE MY_TABLE AUTO_INCREMENT = 111111;";
        DB::unprepared($statement);
    }

    /**
    * Reverse the migrations.
    *
    * @return void
    */
    public function down()
    {
    }
}

Update

//Your migrations here:
Schema::create('users', function (Blueprint $table) {
    $table->bigIncrements('id')->unsigned();
    $table->integer('qualification_id')->nullable();
    $table->integer('experience_id')->nullable();
});

//then set autoincrement to 1000
DB::update("ALTER TABLE users AUTO_INCREMENT = 1000;");

Answer 3

Prashant's method works without problems. But as it is said earlier, don't put use DB; in the top of your file.

And here are the results after php artisan migrate

Answer 4

Most tables work with increments incrementing from the next biggest integer.

One can always insert an integer, that is higher than the current autoincrementing index. The autoincrementing index will then automatically follow from that new value +1 up.

So, if you have a freshly minted table your current index is 0, the next key will be 0 + 1 = 1.

What we want is a primary key that starts at 1000, so what we do is insert a record with id value of 999, so the next insert will become 1000.

In code:

 $startId = 1000;

 DB::table('users')->insert(['id'=> $startId - 1]);
 DB::table('users')->where('id',$startId - 1)->delete();

and now you have an empty table where the next insert id should be 1000.

Please note that if you have values to seed into the table with id values < startId you need to do that before you execute these statements. Otherwise the database will throw an constraint violation error.

This should work database agnostic, but if there's a database that does not follow this autoincrement rule i'd love to hear about it.