sql - 创建自定义 "auto-increment"复合主键？

Question

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

父表有一个自动编号 PK(用于存储销售票头)。这里的一个记录是指票上的记录。

子表用于存储票证详细信息。此处的一条记录是票中的一个行项目(例如可乐、火星棒等)

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

父表的PK

使订单项在此票证中唯一的编号

如果我使用 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 取自父表； Col2 在 Col1 改变后重置；由 Col2 组成的 Col1 是唯一的。

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

Answer 1

举个例子:

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

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

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

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 设置为“非空”，因为多次插入会导致重复键。因此我不能使用主键，而是使用聚集索引。