prevent duplicate invoice no. against particular ID

Question

I have confusion on how to prevent duplicate InvoiceNo against CompanyId. I just have prepare an Invoice project that has field like CompanyId and InvNo. Both do not have Unique Keys because Company ID and InvoiceNo both have to repeated. as per below

CompanyID   InvNo
1           1
2           1
1           2
3           1
4           1
1           3

Now I want to fire a raiserror on duplicate InvoiceNo against a particular CompanyId. How do I implement this. Important: if i create a unique index then duplicate records will not be allowed and it is important to allow except against particular CompanyId

Answer 1

What you are looking for is a Unique Constraint composed of both CompanyId and InvNo. This will let you create only one InvoiceNo = 1 for CompanyId = 1 and will automatically RaisError if you try to insert a duplicate. It will also let you insert InvoiceNo = 1 for CompanyId = 2 thereby (hopefully) satisfying your requiements

This is how I would do it in SQL Server

ALTER TABLE YourTableName
ADD UNIQUE CONSTRAINT InvoiceIdMustBeUniqePerCompany (CompanyId, InvNo)

Answer 2

Your question is not all that straight forward, but assuming you're asking what I think you're asking, it goes something like this...

You need a cable called NextInvoiceNumber, comprising of CompanyID and NextInvoiceNo. Creating a new Company should create a new NextInvoiceNumber - so perhaps use an insert trigger on your Companies table for that...

Write a function to get the next Invoice id for a specific company, and then incremenent the value in the NextInvoiceNumber table (all inside a common transaction).

So, in pseudo code, something like

    function GetNextInvoiceNo(CompanyIDCode){

begin transaction;
result = Select NextInvoiceNo from NextInvoiceNumber where CompanyID = CompanyIDCode;
update NextInvoiceNo set NextInvoiceNo = NextInvoiceNo + 1 where CompanyID = CompanyIDCode;
commit transaction;
return result;
}

This function ideally belongs on your database server as a UDF.

Answer 3

From the sample data it appears that the combination of CompanyID + InvNo is unique. If that's true you can create a key on those two fields and upon an attempt to insert a duplicate InvNo that has already been used for a particular CompanyID an error would be thrown.

create table Invoice
(
CompanyID int,
InvNo int,
Primary Key(CompanyID, InvNo)
)