auto increment id with respect to the rest of the primary key

Question

I have a table that looks like this:

| PK | Col Name | Type |
|----+----------+------|
| X  | ParentId | int
| X  | Id       | int

I tried to set Id to be the identity, and ParentId is set on the parent table. What I was hoping the data would look like would be:

| ParentId | Id |
|----------+----|
| 1        | 1  |
| 1        | 2  |
| 1        | 3  |
| 2        | 1  |
| 2        | 2  |
| 3        | 1  |

but instead it looks like:

| ParentId | Id |
|----------+----|
| 1        | 1  |
| 1        | 2  |
| 1        | 3  |
| 2        | 4  |
| 2        | 5  |
| 3        | 6  |

which makes sense in hindsight. Is the desired effect achievable in some way?

Answer 1

If you want to get the desired output from only the ParentId, i also suggest this, you can use this :

select ParentId, 
ROW_NUMBER() OVER (PARTITION BY parentid order by parentid) as Id
from Foo

SQL HERE

But if you still want to use in the table, you can create a INSTEAD OF INSERT trigger on your table, here is the trigger you can use :

create trigger dbo.trInsertFoo on dbo.Foo instead of insert
as begin
    insert into dbo.Foo
            (ParentId, Id)
    select  ParentId,
            Id =
            isnull( (select max(Id)
                    from    dbo.Foo
                    where   ParentId = i.ParentId), 0) +
            row_number() over (partition by ParentId order by (select 1))
    from    inserted i;
end;

Simplified version of the above trigger

create trigger dbo.trInsertFoo on dbo.Foo instead of insert
as begin
    insert into dbo.Foo
            (ParentId, Id)
    select  ParentId,
            Id =
            (select isnull(max(Id), 0) + 1 from dbo.Foo where ParentId = i.ParentId)
    from    inserted i;
end;

But this one will not work for batch insert like :

INSERT INTO Foo (ParentId) VALUES (1), (1), (1), (2), (2), (3)`