Auto-increment id based on composite primary key
Note. Using Sql Azure and Entity Framework 6
Let's say I have the following invoice table (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 sequentially for each StoreId, rather than independently 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]
magnification based on [StoreId]
?
Possible options:
a) Ideally, some parameter [InvoiceId] INTEGER NOT NULL IDENTITY_BASED_ON([StoreId])
would be really helpful, but I doubt it exists ...
b) A way to set a default value from a function return based on another column? (AFAIK, you cannot reference another column by 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 deal with this in Entity Framework (first code without migration) using DbContext.SaveChangesAsync
override or set custom insert query?
Note. I understand that I can do this with a stored procedure to insert the invoice, but I would rather avoid it unless that is the only option.
source to share
You should stick with an auto incremental integer primary key, it's much easier than dealing with a composite primary key, especially when you are linking things to an invoice.
To generate an InvoiceNumber for a user that increments each store, you can use a function ROW_NUMBER
divided into sections by StoreId
and ordered with your auto incrementing primary key.
This is shown in the following example:
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
source to share
After playing around with the answer provided by @Jamiec in my solution, I decided to go the TRIGGER route in order to keep the invoice number and work better with Entity Framework. Also, since it ROW_NUMBER
doesn't work in INSERT (AFAIK), I use instead MAX([InvoiceNumber])+1
.
CREATE TABLE [dbo].[Invoice] (
[InvoiceId] INTEGER NOT NULL,
[StoreId] UNIQUEIDENTIFIER NOT NULL,
[InvoiceNumber] INTEGER NOT NULL,
CONSTRAINT [PK_Invoice] PRIMARY KEY CLUSTERED ([InvoiceId] ASC)
);
CREATE TRIGGER TGR_InvoiceNumber
ON [Invoice]
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO [Invoice] ([InvoiceId], [StoreId], [InvoiceNumber])
SELECT [InvoiceId],
[StoreId],
ISNULL((SELECT MAX([InvoiceNumber]) + 1 FROM [Invoice] AS inv2 WHERE inv2.[StoreId] = inv1.[StoreId]), 1)
FROM inserted as inv1;
END;
This allows me to customize the EF class like this:
public class Invoice
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int InvoiceId { get; set; }
public Guid StoreId { get; set; }
[DatabaseGenerated(DatabaseGeneratedOption.Computed)]
public int InvoiceNumber { get; set; }
}
source to share