Laravel的多个自动递增列

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.

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)

Two auto-incrementing columns? That way lies madness.

Set your order column to default to a number, eg: 0, and only modify it if you want to set something's ordering to be custom. Use order as your primary sort key, and id or timestamp as the secondary.

http://sqlfiddle.com/#!9/d55a51/2

CREATE TABLE posts(
    `id` INTEGER AUTO_INCREMENT PRIMARY KEY,
    `order` INTEGER DEFAULT 0,
    `timestamp` DATETIME DEFAULT NOW()
);

INSERT INTO posts ( `order`, `timestamp` ) VALUES
  ( 0,  "2019-01-01 00:00:01" ),
  ( 0,  "2019-01-01 00:00:02" ),
  ( 1,  "2019-01-01 00:00:03" ),
  ( -1, "2019-01-01 00:00:04" ),
  ( 0,  "2019-01-01 00:00:05" );

SELECT * FROM posts ORDER BY `order`, `id`;

Output

id  order   timestamp
4   -1  2019-01-01T00:00:04Z
1   0   2019-01-01T00:00:01Z
2   0   2019-01-01T00:00:02Z
5   0   2019-01-01T00:00:05Z
3   1   2019-01-01T00:00:03Z