1 year ago

Incrementing a column other than primary key

Posted 1 year ago by audunru

I'm looking into how I can auto-increment a column in my database other than the primary key.

Here goes:

In my app, a Company can have many Products. A Product belongs to only one Company.

A Product has a primary key, but a "code" as well, which is an unsigned integer starting at 1. If there are two companies with 5 products each, both companies will have products with the codes 1, 2, 3, 4 and 5.

Products can be soft-deleted, so if product with code 5 is deleted, the next new product should have code 6.

Codes should be auto-generated. After a Product has been stored, the code cannot be changed.

My Company model has this function that I use to add new products.

    public function addProduct(Product $product)
        $product->code = Product::withTrashed()->max('code') + 1;

        return $this->products()->save($product);

The problem with the code above is that in the event that two users are adding products at the same time, potentially the same code (max + 1) could be returned, giving one of them an error. It also does not feel good to set the code in the Company model instead of the Product model.

The migration for my "products" table ensures that a single Company cannot have two products with the same code (see the unique index):

        Schema::create('products', function (Blueprint $table) {
            $table->unique(['company_id', 'code']);

I use mariadb as the database but I'm open for suggestions if there is some other database that can solve this at the database level.

Please sign in or create an account to participate in this conversation.