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');

score 12 · Accepted Answer · answered Mar 21 '17 at 15:04

12

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.

answered Mar 21 '17 at 15:04

Niklas S.

1,046
10
22

2

Hi. I was able to drop the primary key without passing the `index_name`. `$t->dropPrimary()`. Laravel auto-detects the name of the primary key index for the specific column. I am using Laravel 5.5 with PostgreSQL – Tumaini Mosha Apr 09 '18 at 09:50
4

This does not work with MySQL: `SQLSTATE[42000]: Syntax error or access violation: 1075 Incorrect table definition; there can be only one auto column and it must be defined as a key` – Adam Dec 14 '18 at 07:16

3

I was able to get this to work, but it needs to be in 3 different sections:

Schema::create('table', function(Blueprint $t) {
    // Add the Auto-Increment column
    $t->increments("some_column");
    // Plus all other fields except actual PK
});
Schema::table('table', function(Blueprint $t) {
    // Remove the primary key
    $t->dropPrimary("table_some_column_primary");
});
Schema::table('table', function(Blueprint $t) {
    // Set the actual primary key
    $t->primary(array("id"));
});

– Dan D Jan 25 '20 at 18:14

This worked for me (it's imporant to set an index for the autoincrement column) `$table->uuid("id");` `$table->increments("aux_id");` // other colums definitions `...` //auto increment needs to be an index (not necessarily primary) `$table->index(array("aux_id"));` //after setting "normal" index, drop primary `$table->dropPrimary();` //add primary to the field you want `$table->primary("id");` – boryn Jul 06 '20 at 08:17
Thanks @DanD - I was able to combine the last two sections into one. Maybe something is different now with Laravel 10. I had trouble using the same index name pattern but was able to use the technique from [this answer](https://stackoverflow.com/a/45883428/1575353) - e.g. `$indexesFound = Schema::getConnection()->getDoctrineSchemaManager()->listTableIndexes('table');` and then `$table->dropPrimary($indexesFound['primary']->getName());` Obviously one could guard against the scenario where `$indexesFound['primary']` is not set and handle that accordingly. – Sᴀᴍ Onᴇᴌᴀ May 01 '23 at 16:26

insign · Answer 2 · 2020-04-23T00:02:33.077

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;

edited Apr 23 '20 at 00:02

answered May 01 '18 at 19:32

insign

5,353
1
38
35

4

This can lead to bad things when dealing with concurrent scenarios. – Luiz May 01 '18 at 22:04
1

just don't delete records? How about $model->dataset = Model::max('dataset') + 1; – Joeri Nov 13 '18 at 15:48
@Luiz auto-increment, is probably bad for any concurrent scenario. – insign Jan 18 '20 at 15:40
@insign Thanks, but that's not the subject – Luiz Jan 19 '20 at 16:03
if you delete a row, you'll get duplicated values – WoLfulus Apr 22 '20 at 16:22
@WoLfulus better now? – insign Apr 23 '20 at 00:02

score 2 · Answer 3 · edited Dec 08 '20 at 13:16

2

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

edited Dec 08 '20 at 13:16

Krzysztof Madej

32,704
10
78
107

answered Dec 08 '20 at 11:32

Surajit Sil

21
3

Please comment how this code solves the issue. – Krzysztof Madej Dec 08 '20 at 13:16

score 0 · Answer 4 · answered Nov 21 '17 at 06:55

0

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

answered Nov 21 '17 at 06:55

Rowan Christmas

199
2
5

score 0 · Answer 5 · answered Mar 02 '22 at 09:18

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

is it possible to use `gerneratedAs()` with a starting value? for example starting att 1000 and up from there? — Tim Bogdanov, Oct 31 '22 at 21:26

score 0 · Answer 6 · edited Aug 05 '22 at 08:54

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

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Fjarlaegur, Aug 02 '22 at 12:43
how would you approach my issue on this topic? https://stackoverflow.com/questions/74269132/non-primary-auto-incrementing-integer-in-laravel-migration — Tim Bogdanov, Oct 31 '22 at 21:22

How to set auto increment into non primary key?

6 Answers6

Linked