MSSQL Server, grouped auto increment key for composite key

This post will explains about how to create auto increment key for composite keyed table in MSSQL Server Database.

Background

To create auto increment key in MSSQL Server is simply done by giving an IDENTITY attribute to the key column. For example, this Suppliers table:

CREATE TABLE [dbo].[Suppliers](
	[Id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
	[Name] [nvarchar](max) NULL
)

That we mark the column Id which is the primary key of Suppliers table as an identity column by giving attribute IDENTITY(1,1). So MSSQL Server will generates this value for us incrementally on every new data added.

For the illustration, when you do these insertions:

INSERT INTO [dbo].[Suppliers] ([Name]) VALUES ('Jon')
GO
INSERT INTO [dbo].[Suppliers] ([Name]) VALUES ('Kim')
GO

Then you’ll get these results:

Id Name
1 Jon
2 Kim

Now, let’s create another table to store the orders data of suppliers.

CREATE TABLE [dbo].[Orders](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[SupplierId] [int] NOT NULL,
	[Date] [datetime] NOT NULL,
	[Amount] [decimal](18, 2) NOT NULL,
 CONSTRAINT [PK_dbo.Orders] PRIMARY KEY CLUSTERED (
	[Id] ASC,
	[SupplierId] ASC
 ) ON [PRIMARY],
 CONSTRAINT [FK_dbo.Orders_dbo.Suppliers_SupplierId] FOREIGN KEY([SupplierId])
	REFERENCES [dbo].[Suppliers] ([Id])
	ON DELETE CASCADE
)

This table has 2 primary keys, the Id as the main primary key, and SupplierId as another primary key that also act as a foreign key for Suppliers table.

Same thing as the Suppliers table, we make the Id column of Orders table as an identity column. So when you do these insertions:

INSERT INTO [dbo].[Orders] ([SupplierId] ,[Date] ,[Amount])
    VALUES (1 ,getdate(),100)
GO
INSERT INTO [dbo].[Orders] ([SupplierId] ,[Date] ,[Amount])
    VALUES (1 ,getdate(),200)
GO
INSERT INTO [dbo].[Orders] ([SupplierId] ,[Date] ,[Amount])
    VALUES (1 ,getdate(),300)
GO
INSERT INTO [dbo].[Orders] ([SupplierId] ,[Date] ,[Amount])
    VALUES (2 ,getdate(),150)
GO
INSERT INTO [dbo].[Orders] ([SupplierId] ,[Date] ,[Amount])
    VALUES (2 ,getdate(),250)
GO

You’ll get these results:

SupplierId Id Date Amount
1 1 2015-08-24 09:13:56.960 100.00
1 2 2015-08-24 09:13:56.963 200.00
1 3 2015-08-24 09:13:56.967 300.00
2 4 2015-08-24 09:13:56.970 150.00
2 5 2015-08-24 09:13:56.970 250.00

There is nothing wrong with the results. But for some reason you want the results to be like these:

SupplierId Id Date Amount
1 1 2015-08-24 09:13:56.960 100.00
1 2 2015-08-24 09:13:56.963 200.00
1 3 2015-08-24 09:13:56.967 300.00
2 1 2015-08-24 09:13:56.970 150.00
2 2 2015-08-24 09:13:56.970 250.00

That the value of Id reset back to 1 when new supplier added. The incremental values is grouped by supplier. Thus, you’ll get wider range for your primary key values. Say that the maximum integer value is 2.147.483.647, then by combine it with another integer value from another primary key you’ll get 2.147.483.647 x 2.147.483.647. This way we can get another advantage of using Composite Key.

Auto increment key for composite key

So, giving IDENTITY attribute is not enough to achieve this expectation. We need to modify the Id value of Orders to reset back to 1 on every insertion that been made. Then we need to create a Trigger to do this hijacking.

The idea is — we’re gonna use INSTEAD OF INSERT trigger, so we can modify the values before data get inserted. In the trigger we determine the highest value from existing data for same inserted supplier. Plus it with one, then we use the result as the new Id of Orders.

Here’s the trigger looks like:

CREATE TRIGGER [dbo].[Orders_Auto_Composite_Key]
ON [dbo].[Orders]
INSTEAD OF INSERT
AS 
BEGIN
    -- We don't want to return any affected row number
    SET NOCOUNT ON;

    -- Open identity insertion access
    SET IDENTITY_INSERT [dbo].[Orders] ON;

    -- Get the higher value of [Id] for same supplier
    DECLARE @last_id as bigint;
    SET @last_id = ISNULL((SELECT MAX([Id]) FROM [dbo].[Orders] WHERE [SupplierId] IN (SELECT [SupplierId] FROM INSERTED)), 0);
    
    -- Increment
    SET @last_id = @last_id + 1;
	
    -- Do insertion with modified PK value
    insert into [dbo].[Orders] ([Id],[SupplierId],[Date],[Amount]) 
		select @last_Id,[SupplierId],[Date],[Amount] from INSERTED;
    
    -- Close identity insertion access
    SET IDENTITY_INSERT [dbo].[Orders] OFF;
END
GO

Try to delete all data on Orders table and then do above insertion operation for Orders table again. Now, what you’re expecting is come true. You should get these composite keys generated:

SupplierId Id
1 1
1 2
1 3
2 1
2 2

Sequences

Just one more problem. Let’s try by example. Now please delete the last record in Orders table.

DELETE FROM [dbo].[Orders] WHERE SupplierId = 2 AND Id = 2

Then add new order data for Supplier 2:

INSERT INTO [dbo].[Orders] ([SupplierId] ,[Date] ,[Amount])
    VALUES (2 ,getdate(),350)

Now look at the data in Orders table. And.. oops! The Id 2 with SupplierId 2 seems rise from the grave, but with different value!

That can’t be right, the new added key value for column Id should be 3 instead of 2, because 2 already deleted.

Yes, that was because of we only told the trigger to get the maximum value of Id on the data, we don’t care about deleted or updated data. Since 2 has been already deleted then it will retrieves 1, when it incremented — it became 2.

To take care about the deleted or updated data so the Id generation is more make sense, then we should log every changes made to the Orders data. But of course we won’t create a log like a true log data, cause that would be too much.

All we need to do is just one table helper to record the latest highest Id value of the composite keyed table. Let’s call this table as Sequences. We will record all changes made by the trigger to this table.

CREATE TABLE [dbo].[Sequences](
	[Model] [nvarchar](50) NOT NULL,
	[Constrains] [nvarchar](300) NOT NULL,
	[LastId] [bigint] NULL,
 CONSTRAINT [PK_Sequence] PRIMARY KEY CLUSTERED (
	[Model] ASC,
	[Constrains] ASC
 )
)

There are just 3 columns:

Mode is the name of table
Constrains is the combination of primary keys value
LastId would be the last entered Id of the table

Now let’s modify our trigger to be like this:

ALTER TRIGGER [dbo].[Orders_Auto_Composite_Key]
ON [dbo].[Orders]
INSTEAD OF INSERT
AS 
BEGIN
    -- We don't want to return any affected row number
	SET NOCOUNT ON;

    -- Open identity insertion access
    SET IDENTITY_INSERT [dbo].[Orders] ON;

	    -- The model name
    DECLARE @model as nvarchar(max);
    SET @model = '[dbo].[Orders]';

    -- Build sequence unique key
	DECLARE @constrains as nvarchar(max);
	SET @constrains = (SELECT '[SupplierId]=' + CAST([SupplierId] AS nvarchar(max)) FROM INSERTED);

    -- Get the higher value of [Id] for same supplier
    DECLARE @last_id as bigint;
    SET @last_id = ISNULL((SELECT MAX([Id]) FROM [dbo].[Orders] WHERE [SupplierId] IN (SELECT [SupplierId] FROM INSERTED)), 0);

    -- Get last PK ([Id]) value from sequence
    DECLARE @seq_id as bigint;
	SET @seq_id = ISNULL((SELECT [LastId] FROM [dbo].[Sequences] WHERE Model = @model AND [Constrains] = @constrains), 0);

    -- Pick the highest value to be use as PK value
    IF (@seq_id > @last_id)                        
        SET @last_id = @seq_id;
    
    -- Increment
    SET @last_id = @last_id + 1;

    IF (@seq_id > 0)
        -- There is an already sequence for this table, just update it
		UPDATE [dbo].[Sequences] SET [LastId]=@last_id WHERE [Model] = @model AND [Constrains] = @constrains;
	ELSE
        -- Otherwise, make a new one
		INSERT INTO [dbo].[Sequences] ([Model], [Constrains], [LastId]) VALUES(@model, @constrains, @last_id);
	
    -- Do insertion with modified PK value
	insert into [dbo].[Orders] ([Id],[SupplierId],[Date],[Amount]) 
		select @last_Id,[SupplierId],[Date],[Amount] from INSERTED;
    
    -- Close identity insertion access
    SET IDENTITY_INSERT [dbo].[Orders] OFF;

    -- EF needs these values
    SELECT @last_Id AS [Id],[SupplierId] FROM INSERTED;
END

Done. Now let’s do the test by insert new value on Orders table:

INSERT INTO Orders ([SupplierId], [Date], [Amount]) 
	VALUES(2, getdate(), 400)

New order data should be added to the Orders table with SupplierId=2 and Id=3. If you check the Sequences table there will be new record added:

Model Constrains LastId
[dbo].[Orders] 1[SupplierId]=2 3

Trigger will look into the LastId as consideration to generate new Id when new record added into Orders table.

So when you delete this last record:

DELETE FROM Orders WHERE SupplierId=2 AND Id=3

Then insert new data again, you’ll get SupplierId=2 and Id=4 instead of SupplierId=2 and Id=3.

Conclution

I hope this post can help whoever has an issue to generate the auto increment key for composite keyed table. But however, there should be smarter way to resolve the auto generate for composite key out there.

Just let me know when you have any better idea.

For you who work with Entity Framework, specially for Code First, I wrote a helper module that can generate the trigger that I explain above automatically in here.

 

adiono