Identity-like column but based on Group By criteria

Question

In my SQL Server 2012 database, I'm creating a "Tasks" table that will have a compound primary key composed of three fields:

Issue_ID [int] NOT NULL,
Issue_Sub_ID [int] NOT NULL,
Task_ID [int] NOT NULL

Issue_ID and Issue_Sub_ID are foreign keys to other tables. In the table I'm creating, there can be many tasks associated with each Issue_ID / Issue_Sub_ID combination.

What I would like to do is establish a default value for the Task_ID column, similar to if I used IDENTITY(1,1), but that will auto-increment based on the Issue_ID / Issue_Sub_ID group. For example, the Task_ID results would look as follows, given the provided Issue_ID / Issue_Sub_ID values:

Issue_ID     Issue_Sub_ID     Task_ID
========     ============     =======
12345             1              1
12345             1              2
12345             1              3
12345             2              1
12345             2              2
67890             2              1
67890             2              2
67890             2              3

I'm familiar with the ROW_NUMBER() OVER(PARTITION BY Issue_ID, Issue_Sub_ID ORDER BY Issue_ID, Issue_Sub_ID) possible solution but, as I'd like this column to be a part of the compound primary key of the table, I don't think that will work.

Thanks all in advance.

Identity-like column but based on Group By criteria

1 Answers