Laravel auto-increment on non-primary field

Question

There is my migration. I want id field being auto-incremented but not primary. Is it possible? This migration throws an exception Syntax error or access violation: 1075 Incorrect table definition; there can be only one auto column and it must be defined as a key

Schema::create('tests', function (Blueprint $table) {
    $table->increments('id');
    $table->unsignedInteger('project_id');
    $table->unsignedInteger('model_id'); 
    $table->timestamps();

    $table->dropPrimary('id');

    $table->foreign('project_id')
        ->references('id')
        ->on('projects')
        ->onDelete('cascade');

    $table->primary(['project_id', 'model_id']);
});

score 2 · Answer 1 · answered Dec 18 '18 at 08:26

2

This is not Laravel error. you can't have two auto increment column in mysql table. (of course if you are using mysql) but I think none of any relational database gives you ability to have two auto increment columns.

But there is another way to do that. look at this question

answered Dec 18 '18 at 08:26

Malkhazi Dartsmelidze

4,783
4
16
40

But `id` is only auto increment column in my case – fiter Dec 18 '18 at 08:35
i have the same problem, id should be auto incrementing column but i want to use another column as primary key. did you find any solution? @fiter – siri Mar 23 '19 at 14:23
@siri sorry, nope – fiter Mar 24 '19 at 15:10

score 0 · Answer 2 · answered Dec 18 '18 at 09:02

0

This is not a Laravel error but a MySQL error. As the error message says: it must be defined as a key. You are dropping the primaryKey constraint on the id column. You should try setting it as an index.

try the following $table->unsignedInteger('id')->index()->autoIncrement();. This will make it an AI integer and also an index but not a PK.

answered Dec 18 '18 at 09:02

Fjarlaegur

1,395
1
14
33

4

unfortunately I'm getting an error in this case : `Syntax error or access violation: 1068 Multiple primary key defined (SQL: alter table 'tests' add primary key 'tests_project_id_model_id_primary'('project_id', 'model_id'))` – fiter Dec 18 '18 at 14:48

Laravel auto-increment on non-primary field

2 Answers2