CODEWITHSUNDEEP
sqlsql-serverauto-increment
bbrian51
bbrian51

Reputation: 43

Auto Increment Order Number (multiple companies in one table)

I have a primary key OrderHeaderID that auto increments, but i also need to auto increment the OrderNumber based what company the order is placed for.

Current Strategy: (On my insert statement to the table)

(SELECT MAX(OrderNumber) + 1 FROM  OrderHeader WHERE CompanyID = @CompanyID)

Issue: once i started testing with some volume, i started getting duplicate key errors:

Table OrderHeader:

OrderHeaderID CompanyID OrderNumber
1             1         10000
2             1         10001
3             1         10002

4             2         10000
5             2         10001
6             2         10002

Upvotes: 4

Views: 3247

Answers (3)

NicVerAZ
NicVerAZ

Reputation: 407

I would use a function to auto-increment the OrderNumber per a given ID.

Here is what I put together in 2 minutes. It does what you are asking.

create table dbo.OrderHeader (
    OrderHeader int identity(1,1) primary key
    ,CompanyID  int
    ,OrderNumber int
    )
go

create function dbo.NextOrderNumber (
    @CompanyID int
    )
returns int
as
begin
    declare @result int;

    select  @result = OrderNumber + 1
    from    OrderHeader
    where   CompanyID = @CompanyID 

    if @result is null
        set @result = 10000

    return  @result;
end
go  

insert into OrderHeader select 1, dbo.NextOrderNumber(1)

insert into OrderHeader select 2, dbo.NextOrderNumber(1)
insert into OrderHeader select 2, dbo.NextOrderNumber(1)

insert into OrderHeader select 1, dbo.NextOrderNumber(1)

select *
from OrderHeader

Upvotes: 0

Nicholas Carey
Nicholas Carey

Reputation: 74267

take a look at my answer here: sql server: generate primary key based on counter and another column value

For your purposes, you could define your company table thus:

create table dbo.company
(
  id   int         not null primary key ,
  name varchar(32) not null unique      ,
  order_counter    not null default(0)  ,
  ...
)

and your order table thus:

create table dbo.order
(
  company_id   int not null foreign key references dbo.company( id ) ,
  id           int not null ,
  order_number as 100000*company_id + id ,
  ...
  constraint order_AK01 unique nonclustere    ( order_number    ) ,
  constraint order_PK01 primary key clustered ( company_id , id ) ,
)

And set up your "add order" query thus:

declare @new_order_number int

update dbo.company
set @new_order_number = dbo.company.order_counter + 1 ,
    order_counter     = dbo.company.order_counter + 1
where dbo.company.id = @some_company_id

insert dbo.order ( company_id , id ) value ( @some_company_id , @new_order_number )

You have no concurrency (race) conditions: the "interlocked update" takes care of that. Further, you haven't denormalized the database design (1st normal form requires that every row and column intersection be atomic/non-decomposable: that it contain exactly one value from the applicable domain and nothing else. Compound identifers like yours are verboten.)

Easy!

Upvotes: 1

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

To solve your concurrency issue, you'll have to raise the isolation level of your SELECT statement, and then subsequently perform the INSERT inside of the same transaction.

The specific syntax of this will vary for each RBDMS, but here's an example using Sql Server:

BEGIN TRANSACTION
    DECLARE @OrderNumber INT

    SELECT @OrderNumber = MAX(OrderNumber) + 1 
    FROM OrderHeader WITH (XLOCK)
    WHERE CompanyID = @CompanyID

    INSERT... (@OrderNumber)
COMMIT

This places an exclusive lock on the rows read during the SELECT statement, which will prevent another instance of this routine from running concurrently. Instead, the second instance will be blocked until the original process performs the INSERT and COMMIT, at which point the second instance will then proceed to read and lock the newly generated value.

Upvotes: 3

Related Questions