Multiple Auto-Incrementing Columns For 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.

What is your use case? I mean, let's imagine that the `order` column is also "autoincrementable". So, now you have your `id` and `order` columns increasing on every record.. why would you need this? The order would remain the same sorting by `id` or `order` isn't it?. In which way would you want to change the sorting of your posts? — Kenny Horna, Jul 31 '19 at 02:44
The order column would do as it says, change the order of posts. While the ID will be the primary key. Let’s say I make two posts. Post One: ID: 1, Order: 1. Post Two: ID: 2, Order: 2. — Tyler Shannon, Jul 31 '19 at 16:01
ok...(?). So, if `order` and `id` would have the same values.. why don't you use the `id` column for sorting? This will gives you the exact same result. There's no need to duplicate data (two equal columns). Another alternative is to create an accesor in your model that equals the value of id without the need or storing the value in the database. — Kenny Horna, Jul 31 '19 at 16:05
Now I’ll make comments for each post, but I’m still using the posts ID as the primary key. Basically if I were to swap a post’s ID with another post there would be an issue, because now you have comments from one post now transferred to the other one if that makes sense. But if I were to change the just the order number, no data would be swapped with one another, we’re just changing a number. — Tyler Shannon, Jul 31 '19 at 16:07
Tyler, please explain your full use case or concern. My intention isn't being rude by the way, just that if you explain your full problem/concern then the problem will be clearer to solve. Returning to your comment, you shouldn't change an object id, you'd need to change the foreign key to that object id for another id. That'd be better. The id comes part of the object identity, it shouldn't be manipulated. — Kenny Horna, Jul 31 '19 at 16:11

score 0 · Answer 1 · answered Jul 31 '19 at 00:47

0

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)

answered Jul 31 '19 at 00:47

sultania23

322
3
11

score 0 · Answer 2 · answered Jul 31 '19 at 01:04

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

Multiple Auto-Incrementing Columns For Laravel

2 Answers2