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

For laravel 8+ there's a [new modifier](https://laravel.com/docs/8.x/migrations#column-modifiers) you can do something like `$table->id()->from(1000);` I understand this question for `5.1` — Salam, Sep 30 '20 at 15:32

score 38 · Accepted Answer · edited Feb 27 '19 at 11:41

38

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
//after creating the table
DB::update("ALTER TABLE users AUTO_INCREMENT = 1000;");

edited Feb 27 '19 at 11:41

myckhel

800
3
15
29

answered Dec 10 '15 at 07:39

I have pasted that line under my migration, it output an error: `The use statement with non-compound name 'DB' has no effect` – Anshul Mishra Dec 10 '15 at 07:47
1

I think you are using DB at the top (use DB;) that is why you are getting this error, just remove this, and I have updated my answer have a look. – Dec 10 '15 at 08:12
1

This not work for all databases. Example, not work for sqlite. – abkrim Nov 23 '16 at 17:35
If like use this on sqlite database, use `DB::update("UPDATE SQLITE_SEQUENCE SET seq = $num WHERE name = '$table'") ` where $num it's your new last increment number, and $table it's table. http://www.sqlite.org/autoinc.html – abkrim Nov 23 '16 at 17:49

score 12 · Answer 2 · answered Sep 22 '20 at 23:37

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

`$table->id()->from(1000);` inserts data id from 1000 – Mohamed Raza Mar 18 '22 at 18:23 — Mohamed Raza, Mar 18 '22 at 18:23

score 8 · Answer 3 · answered Mar 08 '18 at 15:52

8

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.

answered Mar 08 '18 at 15:52

Captain Hypertext

2,446
4
27
36

what if I have many colums with increments datatype in the same table for example product_id , counter_id and I want to set the starting value for counter_id only , is this will affect product_id as well ? – Hussam Adil Sep 04 '19 at 11:33
The counter is defined at the table level. You cannot have multiple AUTO_INCREMENT columns on one table in mysql, so what you are asking is not possible. – Captain Hypertext Sep 04 '19 at 11:47

Tschallacka · Answer 4 · 2017-08-22T08:52:59.393

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.

Yea, decided to post a database agnostic answer that should be useable on any database engine, instead of the database specific hacks. — Tschallacka, Aug 22 '17 at 09:23

score 4 · Answer 5 · edited Nov 22 '19 at 05:16

//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;");

Thanks. In laravel 8 you can directly use $table->id()->from(1000); — Ahsan Khan, Sep 23 '21 at 07:23

score -4 · Answer 6 · answered Jun 28 '16 at 14:52

-4

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

answered Jun 28 '16 at 14:52

Szabi Zsoldos

351
6
17

1

Thanks for your answer, but I think you have posted the same answer as Prashant. – Anshul Mishra Jun 29 '16 at 08:01
Please detail your answer and presumption. – Szabi Zsoldos Nov 23 '16 at 18:53

Set Auto Increment field start from 1000 in migration laravel 5.1

6 Answers6

Linked

Related