Auto-incrementing column without primary key

[grimmdude]

I have a need to create an auto-incrementing column which is not to be used as the primary key of the table. There are methods like $table->bigIncrements('id') which create auto-increment columns, but they also assume the column as the primary key.

It seems like it would be ideal to separate the primary key concerns from these methods so the developer can make that choice. For instance, by using the ->primary() method:

// Non-primary
$table->bigIncrements('column');

// Primary
$table->bigIncrements('column')->primary();

Though, I guess at this point it would be a breaking change to do that. Perhaps the solution would be to create new methods specific to non-primary key use?

$table->bigIncrementsNonPrimary('column');

[henzeb]

You mean:

$table->bigInteger('column')->autoIncrement(); 

?

[grimmdude]

This method also defines the auto-increment column as the primary key.

[henzeb]

That's probably because mysql requires auto_increment columns to be part of a key. I tried creating it with an index, but this only adds an alter statement, while indexes are perfectly able to be added to the same statement.

[grimmdude]

It is possible to create an auto-increment column in MySql which isn't the primary key though, right?

[henzeb]

Yes, but it has to be a key. So a unique index would be OK. But since laravel is executing unique indexes as a seperate statement, you need to create the table with index first, and then modify the column to add auto_increment.

[denizgolbas]

Yes there is no Schema method for making auto-increment without primary key. But i used this migration with pgsql:

    public function up()
    {
        Schema::create('foos', function(Blueprint $table) {
            $table->bigInteger('id')->unsigned()->index();
            ...
        });
        DB::statement('CREATE SEQUENCE foos_id_seq START 1 owned by foos.id;');
        DB::statement('ALTER TABLE foos ALTER COLUMN id SET DEFAULT nextval(\'foos_id_seq\'::regclass)');
    }

[grimmdude]

Cool, yea it can be done by using DB::statement() but would be nice if it was possible with Schema methods.

[raghavendra89]

I agree with this. It would be good if we can create auto incrementing columns without setting them as primary key.

In our case we need an auto incrementing order number which will be used in the Invoices. But we don't want to use this order number as primary key. Because we are using a standard UUID column as primary key in all the tables.

[alazark94]

Would it affect the performance if I update it in the static booted() function of the model while creating the model?
Specifically like the following?

protected static function booted()
    {
        static::creating(function (User $user) {
            $latestUser = User::latest()->first();
            if ($latestUser) {
                $latestId = $latestUser->membership_id;
                $user->membership_id = $latestId + 1;
            } else {
                $user->membership_id = 1;
            }
        });
    }

[ghost]

What if you mass insert? You would not have an event for that.

[alazark94]

My current implementation uses this and it's working so far. It works with mass inserts, race conditions. You can try it by running a factory on your model and creating many users.

static::creating(function (User $user) {
            DB::transaction(function () use ($user) {
                DB::table('users')->lockForUpdate()->get();
                $latestIncrementalValue = User::max('membership_id');
                $newIncrementalValue = $latestIncrementalValue + 1;

                $user->membership_id = $newIncrementalValue;
            });
        });

[ghost]

You are locking the whole table....Anyway If you use raw inserts it will not work I guess. Raw inserts don't use the model.

[hafezdivandari]

Fixed on Laravel 11.x #49925

[hafezdivandari]

Ok, you mean something like this?

CREATE TABLE `test` (
  `uuid` char(36) NOT NULL, 
  `display_order` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`uuid`),
  UNIQUE (`display_order`)
);

It's currently not supported by Laravel but I can send a PR to fix this, then you can have:

Schema::create('test', function (Blueprint $table) {
    $table->uuid()->primary();
    $table->bigIncrements('display_order')->unique();
});

is this what you want?

[justinrussell]

If that works, I think that would be perfect.

Just wanted to clarify that MySQL allows increments that aren't a primary key.

[hafezdivandari]

Thanks for pointing that out, you may check this PR #50155

[ghost]

@hafezdivandari will this mean that the autoincrementing column will be fetched from db on each insert? Or it will behave like a default, meaning you have to create and hydrate the model to get it?

[hafezdivandari]

@marius-mcp there is insertGetId method for this (single query that insert and fetch the ID at once): https://laravel.com/docs/11.x/queries#auto-incrementing-ids

PS: PR #50155 has been rejected.

[clovis-mo]

Hi, I was looking for a way to achieve this in my migrations.
This is what i came up with

Schema::table('orders', function (Blueprint $table) {
    $table->unsignedBigInteger('number')->unique()->nullable()->after('id');
});

Schema::table('orders', function (Blueprint $table) {
    $table->unsignedBigInteger('number')->change()->autoIncrement()->from(5000)->primary(false);
});

[alazark94]

Did this actually work for you?

[clovis-mo]

Yes. It worked for me perfectly

[pintend]

Another possible solution (works with singlestore)

Blueprint::macro('autoBigIncrementOnly', function (string $column) {
    /**
     * @var \Illuminate\Database\Schema\Blueprint $this
     */
    return $this->addColumn('autoBigIncrementOnly', $column)->index();
});

SingleStoreSchemaGrammar::macro('typeAutoBigIncrementOnly', function () {
    return 'BIGINT UNSIGNED AUTO_INCREMENT';
});

$table->autoBigIncrementOnly('id');