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

I have a set of parent-child tables (1 to many relationships). I'm building the tables, and have some doubts about the use of PKs and auto-increment.

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

Parent table has an autonumber PK (is used for storing sales ticket header). One record here means on ticket.

父表具有自动编号PK(用于存储销售票证标题)。这里的一条记录意味着票。

Child table is used for storing ticket details. One record here is one line item in the ticket (e.g. coke, mars bar, etc)

子表用于存储票证详细信息。这里有一条记录是机票中的一个项目(例如可乐,火星酒吧等)

I understand that PK for child table should have 2 fields:

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

Parent tables's PK

父表的PK

A number that makes the line item unique within this ticket

在此故障单中使订单项唯一的数字

If I use IDENTITY, it will not "restart" after parent's PK changes.

如果我使用IDENTITY,它将不会在父亲的PK更改后“重新启动”。

I'll show it with an example:

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

A) What SQL does

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) What I want to achieve

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

Note: Col1 is taken from Parent Table; Col2 resets after change in Col1; Col1 composed with Col2 are unique.

注意:Col1取自父表; Col2更改后Col2重置; Col2组成的Col1是独一无二的。

Does SQL Server implement this use of keys? Or should I need to code it?

SQL Server是否实现了密钥的使用?或者我应该编码吗?

3 个解决方案

#1

Just as an example:

仅作为一个例子:

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

The difficulty has been to allow multiple inserts and delayed inserts. HTH

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

Another option is using an instead-of-trigger:

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

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;

Unfortunately it doesn't seem to be possible to set the OrderPosNo "not null" because multiple inserts would lead to a duplicate key. Therefor I couldn't use a primary key and used a clustered index instead.

遗憾的是,似乎无法将OrderPosNo设置为“not null”,因为多个插入会导致重复键。因此,我无法使用主键并使用聚簇索引。

#2

You don't have a one-to-many relationship. You have a many-to-many relationship. A parent can have many items. A coke can belong to more than one parent.

你没有一对多的关系。你有多对多的关系。父母可以有很多项目。可乐可以属于多个父母。

You want three tables. The in-between table is sometimes called a junction table.

你想要三张桌子。中间表有时称为联结表。

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

Note: In the wiki article they only show two columns in the junction table, I believe a best practice is for that table to also have a unique auto-incrementing field.

注意:在wiki文章中,它们只在联结表中显示两列,我相信最佳实践是该表还具有唯一的自动递增字段。

Note: The two joining fields are usually made a unique index.

注意:两个连接字段通常是唯一索引。

#3

You will have to code the logic for this yourself. You might make the task easier by implementing it through triggers, and using window functions (row_number() over (partition by parent_id order by ...).

您必须自己为此编写逻辑代码。您可以通过触发器实现它,并使用窗口函数(row_number()over(由parent_id order by ...分隔)来使任务更容易。

You can also let the primary key be simply an identity column (the parent_id doesn't have to be part of the PK), and have a "Sequence_Num" column to keep track of the int that you want to reset with each parent_id. You can even do this and still set a clustered index on the parent_id / sequence_num cols.

您还可以让主键只是一个标识列(parent_id不必是PK的一部分),并且有一个“Sequence_Num”列来跟踪要用每个parent_id重置的int。您甚至可以执行此操作,并仍在parent_id / sequence_num cols上设置聚簇索引。

IMHO the 2nd option is better because it allows more flexibility without any major drawback. It also makes the window function easier to write because you can order by the surrogate key (the identity column) to preserve the insert order when regenerating the sequence_num's. In both cases you have to manage the sequencing of your "sequenec_num" column yourself.

恕我直言第二个选项更好,因为它允许更多的灵活性,没有任何重大缺点。它还使窗口函数更容易编写,因为您可以通过代理键(标识列)进行排序,以在重新生成sequence_num时保留插入顺序。在这两种情况下,您都必须自己管理“sequenec_num”列的排序。

#1