Set Auto Increment field start from 1000 in migration laravel 5.1

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

Solution 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.

Solution 2

In Laravel 8 you can use from() only for MySQL / PostgreSQL:

Set the starting value of an auto-incrementing field (MySQL / PostgreSQL)

$table->id()->from(...);

This startingValue() method also works but I didn't see this mentioned anywhere in the documentation.

$table->id()->startingValue(...);

Under the hood for mysql it uses:

public function compileAutoIncrementStartingValues(Blueprint $blueprint)
{
    return collect($blueprint->autoIncrementingStartingValues())->map(function ($value, $column) use ($blueprint) {
        return 'alter table '.$this->wrapTable($blueprint->getTable()).' auto_increment = '.$value;
    })->all();
}

Solution 3

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.

Solution 4

//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

 //after creating the table

 DB::update("ALTER TABLE users AUTO_INCREMENT = 1000;");

We need add Prefix table. So we need replace the line

DB::update("ALTER TABLE users AUTO_INCREMENT = 1000;");

by 2 lines below:

$prefix = DB::getTablePrefix();
DB::update("ALTER TABLE ".$prefix."users AUTO_INCREMENT = 1000;");

View more solutions