How to perform auto-increment with dependency on column in SQL Server/Laravel

I am trying to figure out how to do an Auto-increment based on change in values in a different column as shown below. This is what I am getting right now

OtherID | AUTOINCREMENT --------+--------------- A | 1 A | 2 B | 3 C | 4 D | 5 D | 6

This is what I am hoping for

OtherID | AUTOINCREMENT --------+--------------- A | 1 A | 2 A | 3 B |1 C | 1 C | 2

rodrigo.pedra

4 years ago

Level 56

You have to manually manage it, either by using a model event on your Laravel app, or use a INSTEAD OF trigger on your database.

Some references:

What you basically need is to run something like this:

SELECT COALESCE(MAX([AUTOINCREMENT]), 0) + 1 AS NewId
FROM [table]
WHERE [OtherID] = ?

Before inserting a new record and using this new id.

If you do it on the Laravel/PHP side, be sure to use a transaction and use the ->lockForUpdate() when selecting the values, to avoid two concurrent requests ending up with the same id.

More reading:

All Laravel links should work if you replace 9.x by 8.x in case you are using Laravel version 8

1 like

rodrigo.pedra

4 years ago

Level 56

You can also use the ROW_NUMBER() window function to be extra cautious:

SELECT MAX([AUTOINCREMENT]) OVER (PARTITION BY [OtherID]) + 1 AS NewId
FROM [table]
WHERE [OtherID] = ?
UNION
SELECT 1
LIMIT 1;

But here the UNION is needed to handle when a new [OtherId] value is inserted.

Reference

https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver15

1 like

FelipeLevinir

4 years ago

Level 1

Thanks U :)

Please or to participate in this conversation.