How to disable Laravel eloquent Auto Increment?

Question

I need an effective way to disable Laravel from auto incriminating the primary key of the table which I am going to insert data in.

Why? I don't check if there is any duplication between the DB and the inserted data so if there was any duplication I just handles it in a try-catch block.

The problem is if there was any failure Laravel counts it like I have inserted a row. So IDs column is not going to be in this order [1, 2, 3, etc], but in this [1, 4, 8, 20, etc].

I searched a lot about this issue and I have tried to use this line after the declaration of the class:

public $autoincrement = false;

Also

public $incrementing = false;

But they are not working.

I just want to use the AI of my DB. Not Laravel's one.

Answer 1

 **Step 1 add below line in model **
public $incrementing = false;

**Step 2 got to your migration file up function and add below line **

$table->integer('id')->unsigned()->nullable();

Note This up function is my migration function you can see, I am comment // $table->increments('id') and add $table->integer('id')->unsigned()->nullable();

public function up()
{
    Schema::create('tablename', function (Blueprint $table) {
        // $table->increments('id');
        $table->integer('id')->unsigned()->nullable();
        $table->string('name');
        $table->string('info');
        $table->timestamps();
    });
}

Answer 2

In your model:

public $incrementing = false;

In your migration:

//Remove the default $table->id();

//second param is what auto-increments, default is false so can be skipped
$table->unsignedBigInteger('id', false)->primary(); 

A quick comment on all the other outdated or wrong solutions you see here:

• $primaryKey does not need to be overridden unless the primary column is different than 'id'
• You do not need to use the ugly DB transaction just to remove auto-incrementing! Keep using the lovely eloquent models and just use this answer.

Answer 3

You can use

public function up()
{
    Schema::create('users', function (Blueprint $table) {
        $table->integer('matricule')->unsigned();;
        $table->primary(['matricule']);
        $table->string('nometprenom');
        $table->string('age');
        $table->string('adresse');
        $table->string('telephone');
        $table->string('login');
        $table->string('password');
        $table->string('type');
        $table->engine = 'InnoDB';
        
    });
}

Answer 4

This is an example for table created its name site_rules and this is the migration file which i add the following line to make id primary and auto incremented
//add this line to make id auto incremented from a specified value DB::statement("ALTER TABLE site_rules AUTO_INCREMENT = 1;"); and this is the migration file code :

<?php

 use Illuminate\Support\Facades\Schema;
 use Illuminate\Database\Schema\Blueprint;
 use Illuminate\Database\Migrations\Migration;
class SiteRules extends Migration
{
/**
 * Run the migrations.
 *
 * @return void
 */
public function up()
{
    Schema::create('site_rules', function (Blueprint $table){
        $table->increments('id');
        $table->string('name_ar');
        $table->string('name_en'); 
        $table->timestamps();
    });
    //add this line to make id auto increment from a specified value 
    DB::statement("ALTER TABLE site_rules AUTO_INCREMENT = 1;");
}

/**
 * Reverse the migrations.
 *
 * @return void
 */
public function down()
{
    Schema::dropIfExists('site_rules');
}
}

Try it

Answer 5

Sorry for taking your time guys, The issue is if we tried to save any record in MYSQL whether it returned success or failure for any reason (like for duplication in my case),

MYSQL counts that the auto increment number was booked and any other coming record is not going to take it because of there may be more than an insertion process on the DB in the same time and waiting to know if the auto incrementally number is booked or not will cost MYSQL its speed.

So it is not a Laravel issue, its a MYSQL one.

I hope that it may help others.

Thank you all...

Answer 6

if you wish to use a non-incrementing or a non-numeric primary key you must set the public $incrementing property on your model to false.

eg :

class UserVerification extends Model
{
    protected $primaryKey = 'your_key_name'; // or null

    public $incrementing = false;
}

in case of migration :

$table->integer('id')->unsigned(); // to remove primary key 
$table->primary('id'); //to add primary key

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

Answer 7

You can do something like below

Table: author

Columns: id, name, active

class Author extends Model
{
    /**
     * Configure the Model variables
     *
     */
    protected $table = 'author';
    protected $primaryKey = 'id';
    protected $fillable = ['name', 'active']; // eloquent Will use only these columns as you are mentioning them as fillable.

    public static function saveAuthor($data) {
         $author = Author::firstOrNew(['name' => $data['name']]);

            $author->name = $data['name'];
            $author->active = 1;
            $author->save();
    }
}

Here in fillable you define the columns that you want to change or update through model. Rest fields behaviour will take according to mysql definition.

I hope this will help you.

Answer 8

There are 2 solutions to your problem. First one is, as you said, disable the increment column. To do that, just go to your migrations, and change

$table->increment('id)`

to

$table->integer('id')

It will remove the primary key, to set the primary key, just go to your Model file and add this:

protected $primaryKey = 'column_name';

Second solution is what I prefer. Whenever inserting, updating or removing a record and even sometimes reading a record, use laravel's DB transaction. Here is an example:

DB::beginTranscation();
try {
    $model = new Model;
    $model->column_name = $value;
    $model->save();
    DB::commit()
    return;
}
catch(exception $e) {
    DB::rollback();
    return;
}

This approach is better than remove the auto increment. But now it's upto you to choose.

Answer 9

You have to declare your new primary key in your table migration file:

$table->primary('new_key_column');

Of course you have to disable autoincrement in your model as you did.

Answer 10

Try public $incrementing = false;