Is there a way to have an auto-increment / sequence column that is scoped to another column? e.g. in the following table, I’d like the sequence
column to be auto-incremented for the same parent_key
, but set to 1 for a new parent_key
, and auto-incremented thereafter.
+------------+----------+
| parent_key | sequence |
+------------+----------+
| a | 1 |
| a | 2 |
| a | 3 |
| b | 1 |
| b | 2 |
+------------+----------+
An example of this is github issue numbers, they’re scoped to each repo. Obviously I can use (max(sequence) + 1) for a given parent_key
, but: (a) I’m hoping there’s something simpler; and (b) I’m not sure how to make this work without causing a race condition; it’ll probably need to be done as a sub-query during insert, e.g. insert ... (select coalesce(max(sequence), 0) + 1 from mytable where parent_key = 'a')
.