Can a column in a composite unique index auto-increment only to avoid duplicates?

Question

Say you have a table with a composite UNIQUE index – for example, a table of event_contributors holding people (users) who have contributed to an event in different capacities (roles), and who should be listed by role in a specific order. Something like this (simplified to scalar values for illustrative purposes – would of course be a pivot table in real life):

event	user	role	sequence
Garden Party	John	Organiser	3
Garden Party	Jill	Caterer	1
Garden Party	Mike	Organiser	1
Garden Party	Andy	DJ	1
Garden Party	Ross	Organiser	2
Workshop	Andy	Organiser	1
Workshop	Jill	Organiser	2

And when nicely listed:

Catering: Jill
DJ: Andy
Organisers: Mike, Ross and John [← note sequence]

This requires that the combination of event + role + sequence be unique, so you make a UNIQUE index on those three columns.

To avoid duplicates, you can make sequence an auto-incrementing column, but that will make it increment on every new row, so it more or less just becomes synonymous with the primary key (or becomes the primary key). If the presentation order is always fixed, that’s not much of an issue, but if the system needs to allow for reordering (e.g., change "Mike, Ross and John" to "John, Mike and Ross" in the example above), a globally unique sequence column makes things far more complicated.

You can also solve it outside the database, by fetching MAX(sequence) + 1 with the appropriate constraints and passing the returned value explicitly when inserting the new record; but (a) you risk a race condition where someone else inserts a sequence between your fetching the next available sequence number and inserting a record with it; and (b) it’s an extra DB query for every insert.

So I got to wondering if there is a way to define the table/column/index that will make the database essentially do the same thing directly: if there’s already a record with the given event + role + sequence combination, then auto-increment sequence enough that the insertion can succeed; but only auto-increment if necessary to avoid a duplicate, not with every insertion.

Is this something that is possible to do in SQL?

• Martin Smith: Why don't you want it to just increment on every new row and be globally unique in the table? If you are just going to generate a new sequence number every time you encounter a duplicate event, role they aren't adding anything to the uniqueness requirement. The unique constraint isn't enforcing anything other than being a unique row identifier either way.
• Janus Bahs Jacquet: @MartinSmith Mainly for reordering purposes. In the example in the question, if I later want to reorder Mike, Ross and John by putting John first, that’s easy to do if the sequence only needs to be sequential and unique within that event/role combination (update sequence to be 1 where user is John, increment by 1 where sequence >= 1 and user isn’t John); if it’s globally unique, it’s a lot more complicated to switch around, because you can’t update an identity column – and even if you could, you’d have to avoid collision with sequence numbers from other events/roles.
• Martin Smith: You don't need to make it an IDENTITY column - you can still use a SEQUENCE and then the column is updatable. And you can still enforce uniqueness on the combination of three columns rather than just on the sequence column to allow re-ordering within an event, role without having to update other rows from different event, role if you would get a clash on the sequence
• Janus Bahs Jacquet: @MartinSmith Despite the column name used in the example here, I had never heard of SEQUENCE – that could indeed work! If you write that up as an answer, I’ll happily upvote it. Seems the only remaining issue would be one of neatness: 1, 2, 3 just looks neater than 2937, 6352, 11927.
• Martin Smith: You can easily get "nice" sequential display orderings with row_number in order of the sequence so this doesn't need to be displayed to end users (though you would ideally need to keep track of it behind the scenes for UI that allows Updates/Deletes)

Answer 1

In SQL Server the hidden UNIQUEIFIER column for a non unique clustered index key works somewhat similarly to how you suggest but this is a behind the scenes matter and not usable for this purpose.

As a frame challenge I would approach the matter something like this

CREATE SEQUENCE EventContributorSequence 
 AS INT
 START WITH 1 
 INCREMENT BY 1 ; 
CREATE TABLE event_contributors 
 (event_contributor_id INT IDENTITY PRIMARY KEY, /*Stable primary key not subject to updates*/
 [event] varchar(12) NOT NULL, 
 [user] varchar(4) NOT NULL, 
 [role] varchar(9) NOT NULL , 
 [sequence] int DEFAULT NEXT VALUE FOR EventContributorSequence,
 UNIQUE([event], [role], [sequence])
);
 
INSERT INTO event_contributors
 ([event], [user], [role])
VALUES
 ('Garden Party', 'John', 'Organiser'),
 ('Garden Party', 'Jill', 'Caterer'),
 ('Garden Party', 'Mike', 'Organiser'),
 ('Garden Party', 'Andy', 'DJ'),
 ('Garden Party', 'Ross', 'Organiser'),
 ('Workshop', 'Andy', 'Organiser'),
 ('Workshop', 'Jill', 'Organiser')
;

The sequence is incremented for all rows - not just within a partition. But if you prefer looking at "nicer" values you can use (DB Fiddle)

SELECT [event],
 [role],
 [sequence],
 ROW_NUMBER()
 OVER (
 PARTITION BY [event], [role]
 ORDER BY [sequence]) AS CalcSequence
FROM event_contributors
ORDER BY [event],
 [role],
 [sequence] 

The reason I opted for a composite unique constraint on [event], [role], [sequence] rather than just on [sequence] is because the comments indicate that there is a desire to potentially update the [sequence] column to re order rows within a [event], [role] partition and this avoids having to worry about the sequence clashing with something in a different one.

The event_contributor_id column is to provide something stable so the PK doesn't change when you reorder rows.

Answer 2

Well, each role should have a unique ID in the roles table. And every event should have a unique ID in the events table. (These can be auto-incremental, such as by way of marking them as identities.)

Then your event_contributors linking table should just store the combination of those unique IDs and have a unique index on (eventId, roleId, sequence). They won't be auto-incremental in the event_contributors table, so they'll be distinct from the primary key, whatever you end up choosing it to be. But to be honest, I'd just make that the primary key of the event_contributors table itself anyway, and the unique constraint will come with that automatically.

• Janus Bahs Jacquet: I don’t really see how this answers the question... Obviously event, user and role should be foreign keys; I only showed them as scalar values for simplicity, to illustrate the purpose of the table, but that has no relation to what I’m asking at all. The unique index on those three columns is exactly what I already describe in the question, but setting a unique index will not auto-increment the sequence column.
• J.D.: @JanusBahsJacquet TBH, not really clear what your question actually is?...are you saying you want the sequence to auto-increment but only within the scope of the event, and then reset on each event?
• Janus Bahs Jacquet: No, not just each event. The scope is the combination of event + role, so it should reset on each new event + role constellation. So people who perform the same role at the same event (e.g., organisers for the garden party) will have an internal sequencing, as in the example table in the question. If I add another organiser to the garden party, their sequence number would be 4, but if I add a cleaner to the garden party, theirs would be 1 (because there aren’t any existing rows that combine garden party + cleaner).
• J.D.: @JanusBahsJacquet Gotcha. Martin's answer is probably helpful for what you're looking for. The only issue (going back to neatness of the numbers) is by using an auto-incrementing feature like a sequence object, you'll end up with contiguous numbers, which look nice, but make updating the order a little bit of a pain. Typically a sequence / sort field stores numbers with gaps so when you need to add a new row, rarely should you have to update every other row. Better from a database efficiency standpoint.

Answer 3

you can make sequence an auto-incrementing column

That feature does not exist.

Seems like you need UNIQUE(user,role). And use client code or a TRIGGER or a stored function to set sequence.

OR...

If sequence is just for ordering and does not need to have "consecutive" numbers:

sequence INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY(user, role),
INDEX(sequence) -- sufficient for auto_inc

That will provide an ordering that is based on when the row was added to the table. The values of sequence would probably be distinct and usually not consecutive.

With this, there is no need for client code, Triggers, etc.

• Janus Bahs Jacquet: I was thinking about defining sequence with auto-increment and using the composite index as the primary key without auto-incrementing when I wrote that particular bit. UNIQUE(user, role) will not work, since users can have the same role on multiple events. The primary concern is that contributors within each role for each event must be sequentially ordered, so the composite index must be on event, role, sequence. An additional unique index on event, user, role would be a good idea too (to avoid one person being organiser of the same event twice).
• Janus Bahs Jacquet: Using client code to set the sequence is what I’ve always done, and what I’m trying to improve on; using a trigger might be worth looking into, I hadn’t thought about that at all!
• Rick James: The ordering -- Does it need to be just ordering and not consecutive numbers? Check out my addition.
• Janus Bahs Jacquet: Ideally I would like consecutive numbers always starting at 1 – but that's just me being OCD for when I need to look at the actual table once in a while. The actual need is just for ordering.