基于复合主键的自动增量标识 [英] Auto-increment Id based on composite primary key

问题描述

注意：使用Sql Azure&实体框架6

说我有一个商店发票的下表（DB中有多个商店）...

$ b （
[InvoiceId] INTEGER NOT NULL，
[StoreId] UNIQUEIDENTIFIER NOT NULL，
$

CONSTRAINT [PK_Invoice] PRIMARY KEY CLUSTERED（[InvoiceId] ASC，[StoreId] ASC）
）;

理想情况下，我希望InvoiceId连续递增每个StoreId，而不是独立于每个商店。 。

  InvoiceId | StoreId 
 ------------------- 
 1 | 'A'
 2 | 'A'
 3 | 'A'
 1 | 'B'
 2 | 'B'
  

问题： code> [InvoiceId] 根据 [StoreId]

可能的选项：

a）理想的一个 [InvoiceId] INTEGER NOT NULL IDENTITY_BASED_ON（[StoreId]）参数某种方式会真的很有帮助，但我怀疑这是否存在...

b）一种基于另一列从函数返回设置默认值的方法？ （AFAIK，您不能在默认情况下引用另一列）

  CREATE FUNCTION [dbo]。[NextInvoiceId] 
（
 @storeId UNIQUEIDENTIFIER 
）
 RETURNS INT 
 AS 
 BEGIN 
 DECLARE @nextId INT; 
 SELECT @nextId = MAX（[InvoiceId]）+ 1 FROM [Invoice] WHERE [StoreId] = @storeId; 
 IF（@nextId IS NULL）
返回1; 
 
 RETURN @nextId; 
 
 
 CREATE TABLE [dbo]。[发票]（
 [InvoiceId] INTEGER NOT NULL DEFAULT NextInvoiceId（[StoreId]），
 [StoreId] UNIQUEIDENTIFIER NOT NULL，
 
 CONSTRAINT [PK_Invoice] PRIMARY KEY CLUSTERED（[InvoiceId] ASC，[StoreId] ASC）
）; 
  

c）一种在Entity Framework中处理此问题的方法（使用 DbContext.SaveChangesAsync 覆盖或设置自定义插入查询？

注意：我意识到我可以做到一个存储过程插入发票，但我宁愿避免，除非它的唯一选项。

解决方案

您应该坚持使用自动递增的整数主键，这比处理复合主键要简单得多，特别是当将事情与发票相关联时。

为了为用户生成一个InvoiceNumber，每个商店的增量可以使用 ROW_NUMBER 功能由 StoreId 并由您的自动递增主键排序。

下面的示例演示了以下示例：

  WITH TestData InvoiceId，StoreId）AS 
（
 SELECT 1，'A'
 UNION SELECT 2，'A'
 UNION SELECT 3，'A'
 UNION SELECT 4， 'B'
 UNION SELECT 5，'B'
）
选择InvoiceId，
 StoreId，
 ROW_NUMBER（）OVER（PARTITION BY StoreId ORDER BY InvoiceId）AS InvoiceNumber 
 FROM TestData 
  

结果：

 
 InvoiceId | StoreId | InvoiceNumber 
 1 | A | 1 
 2 | A | 2 
 3 | A | 3 
 4 | B | 1 
 5 | B | 2 
 

Note: Using Sql Azure & Entity Framework 6

Say I have the following table of a store's invoices (there are multiple stores in the DB)...

CREATE TABLE [dbo].[Invoice] (
    [InvoiceId] INTEGER NOT NULL,
    [StoreId] UNIQUEIDENTIFIER NOT NULL,

    CONSTRAINT [PK_Invoice] PRIMARY KEY CLUSTERED ([InvoiceId] ASC, [StoreId] ASC)
);

Ideally, I would like the InvoiceId to increment consecutively for each StoreId rather than independent of each store...

InvoiceId | StoreId
-------------------
1         | 'A'
2         | 'A'
3         | 'A'
1         | 'B'
2         | 'B'

Question: What is the best way to get the [InvoiceId] to increment based on the [StoreId]?

Possible options:

a) Ideally a [InvoiceId] INTEGER NOT NULL IDENTITY_BASED_ON([StoreId]) parameter of some kind would be really helpful, but I doubt this exists...

b) A way to set the default from the return of a function based on another column? (AFAIK, you can't reference another column in a default)

CREATE FUNCTION [dbo].[NextInvoiceId]
(
    @storeId UNIQUEIDENTIFIER
)
RETURNS INT
AS
BEGIN
    DECLARE @nextId INT;
    SELECT @nextId = MAX([InvoiceId])+1 FROM [Invoice] WHERE [StoreId] = @storeId;
    IF (@nextId IS NULL)
        RETURN 1;

    RETURN @nextId;
END

CREATE TABLE [dbo].[Invoice] (
    [InvoiceId] INTEGER NOT NULL DEFAULT NextInvoiceId([StoreId]),
    [StoreId] UNIQUEIDENTIFIER NOT NULL,

    CONSTRAINT [PK_Invoice] PRIMARY KEY CLUSTERED ([InvoiceId] ASC, [StoreId] ASC)
);

c) A way to handle this in Entity Framework (code first w/o migration) using DbContext.SaveChangesAsync override or by setting a custom insert query?

Note: I realize I could do it with a stored procedure to insert the invoice, but I'd prefer avoid that unless its the only option.

解决方案

You should stick to an auto-incrementing integer primary key, this is much simpler than dealing with a composite primary key especially when relating things back to an Invoice.

In order to generate an InvoiceNumber for the sake of a user, which increments per-store, you can use a ROW_NUMBER function partitioned by StoreId and ordered by your auto-incrementing primary key.

This is demonstrated with the example below:

WITH TestData(InvoiceId, StoreId) AS
(
    SELECT 1,'A'
    UNION SELECT 2,'A'
    UNION SELECT 3,'A'
    UNION SELECT 4,'B'
    UNION SELECT 5,'B'
)
Select InvoiceId,
        StoreId,
        ROW_NUMBER() OVER(PARTITION BY StoreId ORDER BY InvoiceId) AS InvoiceNumber
FROM TestData

Result:

InvoiceId | StoreId | InvoiceNumber
1         | A       | 1  
2         | A       | 2  
3         | A       | 3  
4         | B       | 1  
5         | B       | 2  

这篇关于基于复合主键的自动增量标识的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！