Laravel query builder setting id to 0 not working

Question

I'm seeding the categories table, when I run:

    DB::table('cats')->insert([
        'id' => 0,
        'parent_cat_id' => null,
        'name' => 'Uncategorized'
    ]);

But the inserted row's id will be 1, if I try to update the id manually on db It's possible.

Anything other than zero works in query builder (e.g. 'id'=>5)

Edit: Currently this hack is working, what's the problem with insert() if update() can change the id to 0?

    DB::table('cats')->insert([
        'id' => 100,
        'parent_cat_id' => null,
        'name' => 'Uncategorized'
    ]);
    DB::table('cats')->where('id',100)->update(['id' => 0]);

My migration schema:

    Schema::create('cats', function (Blueprint $table) {
        $table->increments('id');
        $table->string('name');
        $table->unsignedInteger('parent_cat_id')->nullable();
    });

Answer 1

I solved with this:

public function up()
{
   Schema::create('cats', function (Blueprint $table) {
       DB::statement('SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";');
       //run other migrations 
   }
}

The sql_mode MySQL variable tells MySQL if it should interpret 0 INSERTs as a true 0, not a PRIMARY KEY generation request.

Answer 2

It seems you defined id column with:

$table->increments('id');

That's UNSIGNED INTEGER equivalent.

https://laravel.com/docs/5.4/migrations

Update

To create auto incrementing not unsigned field, do this:

$table->integer('id');
$table->primary('id');

Eloquent assumes that the primary key is an incrementing integer value, which means that by default the primary key will be cast to an int automatically.

https://laravel.com/docs/5.3/eloquent#eloquent-model-conventions