创建自定义“自动增量”复合主键？

我有一组父子表（一对多关系）。我正在建表，对 PK 和自动增量的使用有一些疑问。

父表有一个自动编号 PK（用于存储销售单标头）。这里的一张记录是指票上的记录。

子表用于存储票证详细信息。这里的一条记录是票证中的一项（例如可乐、火星棒等）

我知道子表的 PK 应该有 2 个字段：

1. 父表PK
2. 使订单项在此工单中唯一的数字

如果我使用IDENTITY，当父母的 PK 改变后，它不会“重新启动”。

我将用一个例子来展示它：

A) SQL 的作用

Parent table
Col1  Col2
1     1000
2     2543
3     3454
Note: Col1 is IDENTITY

Child Table
Col1  Col2  Col3
1     1     Coke
1     2     Mars Bar
2     3     Sprite
3     4     Coke
3     5     Sprite
3     6     Mars Bar
Note: Col1 is taken from Parent Table; Col2 is IDENTITY

B）我想要达到的目标

Parent table is the same as above

Child Table
Col1  Col2  Col3
1     1     Coke
1     2     Mars Bar
2     1     Sprite
3     1     Coke
3     2     Sprite
3     3     Mars Bar

注：Col1取自Parent Table； Col1 更改后 Col2 重置； Col1 和 Col2 组成的都是唯一的。

SQL Server 是否实现了键的这种使用？或者我需要编码吗？

举个例子：

create table dbo.tOrders (
    OrderID int not null identity primary key,
    CustomerID int not null
);
create table dbo.tOrderPos (
    OrderID int not null foreign key references dbo.tOrders,
    OrderPosNo int null,
    ProductID int null
);
create clustered index ciOrderPos on dbo.tOrderPos
    (OrderID, OrderPosNo);
go
create trigger dbo.trInsertOrderPos on dbo.tOrderPos for insert
as begin
    update  opo
    set     OrderPosNo = isnull(opo2.MaxOrderPosNo,0) + opo.RowNo
    from    (select OrderID, OrderPosNo,
                    RowNo = row_number() over (partition by OrderID order by (select 1))
            from    dbo.tOrderPos opo
            where   OrderPosNo is null) opo
    cross apply
            (select MaxOrderPosNo = max(opo2.OrderPosNo)
            from    dbo.tOrderPos opo2
            where   opo2.OrderID = opo.OrderID) opo2
    where   exists (select * from inserted i where i.OrderID = opo.OrderID);
end;
go
declare @OrderID1 int;
declare @OrderID2 int;
insert into dbo.tOrders (CustomerID) values (11);
set @OrderID1 = scope_identity();
insert into dbo.tOrderPos (OrderID, ProductID)
values (@OrderID1, 1), (@OrderID1, 2), (@OrderID1, 3);
insert into dbo.tOrders (CustomerID) values (12);
set @OrderID2 = scope_identity();
insert into dbo.tOrderPos (OrderID, ProductID)
values (@OrderID2, 4), (@OrderID2, 5);
insert into dbo.tOrderPos (OrderID, ProductID)
values (@OrderID1, 6);
select * from dbo.tOrderPos;
go
drop trigger dbo.trInsertOrderPos;
drop table dbo.tOrderPos;
drop table dbo.tOrders;
go

困难在于允许多次插入和延迟插入。 华泰

另一种选择是使用替代触发器：

create trigger dbo.trInsertOrderPos on dbo.tOrderPos instead of insert
as begin
    insert into dbo.tOrderPos
            (OrderID, OrderPosNo, ProductID)
    select  OrderID,
            OrderPosNo =
            isnull( (select max(opo.OrderPosNo)
                    from    dbo.tOrderPos opo
                    where   opo.OrderID = i.OrderID), 0) +
            row_number() over (partition by OrderID order by (select 1)),
            ProductID
    from    inserted i;
end;

不幸的是，似乎不可能将 OrderPosNo 设置为“非空”，因为多次插入会导致重复的键。因此，我无法使用主键，而是使用聚集索引。