Laravel的多个自动递增列

Question

I am trying to create a second auto-incrementing column called order for my table called posts. I am creating this auto-incrementing column because I am using jQuery sortable, and you can change the order of posts. But unfortunately, I get this error.

1075 Incorrect table definition; there can be only one auto column and it must be defined as a key

I have tried $table->increments('order')->unique(); but that's what gave me the error. I also tried creating a foreign key constraint in which the order column would reference the 'id' column in the same table. I have also tried making the order parameter fillable in my model.

Here are my migrations.

Posts Migration

Schema::create('posts', function (Blueprint $table) {
    $table->increments('id');
    $table->integer('user_id');
    $table->string('title');
    $table->text('body');
    $table->timestamps();
});

2nd Posts Migration

Schema::table('posts', function (Blueprint $table) {
    $table->increments('order')->unique();
});

The expected result is that when the migration is migrated, it'll create a new column called order in the posts table. This new column should be auto-incrementing. If you need more information please don't be afraid to ask.

Thanks in advance for your help.

Answer 1

You can only have one column marked as identity. However, if there is a mathematical relation between the two identity columns, you can use a calculated column. For example:

create table tbl (id1 int identity(1,100), id2 as id1 + 0)