How to set auto increment into non primary key?

Question

How would I set a database auto increment field that is not a primary key on the creation method?

The only way is using a raw query?

DB::statement('ALTER TABLE table CHANGE field field INT(10)AUTO_INCREMENT');

Answer 1

It is not implemented to do so. However, I found this over at the Laravel Forums:

Schema::table('table', function(Blueprint $t) {
    // Add the Auto-Increment column
    $t->increments("some_column");

    // Remove the primary key
    $t->dropPrimary("table_some_column_primary");

    // Set the actual primary key
    $t->primary(array("id"));
});

This is not tested but should work. I am not sure about how Laravel calls their primary keys, maybe you have to check that first and adapt the dropPrimary() line in order to make it work.

Answer 2

I wanted to have either both id column of type UUID as primary column and also row_number of type integer and auto-increment.

laravel 10 solution:

Schema::create('my_table', function (Blueprint $table) {
    $table->uuid('id');
    $table->bigIncrements('row_number')->index();

    // other columns ...

    $table->dropPrimary('row_number');
    $table->primary('id');
});

Answer 3

Schema::table('table_name', function(Blueprint $table) {
    DB::statement('ALTER TABLE table_name ADD column_name INT NOT NULL AUTO_INCREMENT AFTER after_column_name,  ADD INDEX (column_name)'); 
});

Answer 4

i was using pgsql, i achieved using modifier generatedAs() with laravel 9

Schema::create('form_section', function (Blueprint $table) {
    $table->id()->from(100);
    $table->unsignedInteger('form_id');
    $table->foreign('form_id')->references('id')->on('forms');
    $table->unsignedInteger('section_id');
    $table->foreign('section_id')->references('id')->on('sections');
    $table->unsignedInteger('section_sequence')->generatedAs(); //solution
    $table->timestamps($precision = 0);
});

Answer 5

I do not feel safe doing some workaround at migrations, so I did this inside model file:

/**
 *  Setup model event hooks
 */
public static function boot()
{
    parent::boot();
    self::creating(function ($model) {
        $model->field_here = $model->max('field_here') + 1;
    });
}

If you want disable autoincrementing add this at the beginning of the model file:

public $incrementing = FALSE;

Answer 6

I had a problem like yours, i could resolve It so Try This Example It Works:

Schema::create('etendu', function (Blueprint $table) {



            $table->Increments('id_etendu');

            $table->Integer('TypeActivite');

            $table->Integer('id_activite')->unsigned();

            $table->Integer('id_wilaya')->unsigned();

            $table->Integer('id_user')->unsigned();

            // autoIncrements needs index 
            $table->index(['id_etendu']);

            // Remove the primary key
            $table->dropPrimary("id_etendu");

            $table->foreign('id_activite')
            ->references('id')
            ->on('activite')
            ->onCascade('delete');

            $table->foreign('id_wilaya')
            ->references('id')
            ->on('wilayas')
            ->onCascade('delete');

            $table->foreign('id_user')
            ->references('id')
            ->on('user')
            ->onCascade('delete');
             // make them unique
            $table->unique(['id_etendu','id_activite','id_wilaya']);
            // make it primary key
            $table->primary('id_etendu');
        });

Answer 7

The current answer does not work, auto-incrementing columns must be primary keys. I recommend using firstOrCreate when inserting to get the same level of uniqueness (provided you still want an auto-incrementing key available).