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

Question

我有一组父子表（1对多的关系）。我正在构建这些表，并对使用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来自父表；在Col1更改后，Col2重置；由Col1和Col2组成的内容是唯一的。

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

- ryback

---

你需要编写代码，这方面没有捷径。 - cctan

正如其他人所说，这方面没有直接的实现方法。您需要使用桥接表或编写一些代码来强制执行要求。您可以考虑：您不需要以这种方式存储数据。如果您只需要在票据上显示项目编号，那么可以将其解决为一个显示问题，而不是一个存储问题。请查看SQL中的窗口函数，例如：ROW_NUMBER() OVER (Partition By Col1 ORDER BY Col1, Col2)。 - EBarr

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

难点在于允许多个插入和延迟插入。

另一个选择是使用instead-of触发器：

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设置为“非空”。因此，我不能使用主键，而是使用了聚集索引。

- Manfred Sorg

Answer 2

你没有一个一对多的关系。 你有一个多对多的关系。 一个父级可以拥有多个项目。 一罐可乐可以属于多个父级。

你需要三个表。中间的表有时被称为连接表。

http://en.wikipedia.org/wiki/Junction_table

注意：在维基百科文章中，他们只显示连接表中的两列，我认为最佳实践是该表还应具有唯一的自动递增字段。

注意：通常将这两个连接字段制作为唯一索引。

- Steve Wellens

---

联接表不需要额外的自增列。这是完全无用的，因为它已经有了主键。 - user330315

我并没有说连接表需要一个自增列。我说拥有一个是很好的选择。实际上，许多GUI自动绑定控件都需要一个。而且，使用一个字段来删除/更新记录比总是必须指定两个字段要容易得多。在一些商店中，这是强制性的做法。 - Steve Wellens

Answer 3

你需要自己编写这个逻辑的代码。通过实现触发器并使用窗口函数（row_number() over (partition by parent_id order by ...)），可以使任务变得更容易。

你还可以让主键仅仅是一个标识列（parent_id不一定要成为PK的一部分），并拥有一个“Sequence_Num”列来跟踪每个parent_id想要重置的int值。即使这样做，你仍然可以在parent_id / sequence_num列上设置聚集索引。

我认为第二种选项更好，因为它允许更多的灵活性而没有任何主要缺点。它还使窗口函数更容易编写，因为你可以按照代理键（标识列）排序，以在重新生成sequence_num时保留插入顺序。在两种情况下，你都必须自己管理“sequenec_num”列的排序。

- Giscard Biamby