I have the following problem, I want to have Composite Primary Key like:
PRIMARY KEY (`base`, `id`);
INSERT INTO table(base) VALUES ('A')
Ever since someone posted a similar question, I've been pondering this. The first problem is that DBs don't provide "partitionable" sequences (that would restart/remember based on different keys). The second is that the
SEQUENCE objects that are provided are geared around fast access, and can't be rolled back (ie, you will get gaps). This essentially this rules out using a built-in utility... meaning we have to roll our own.
The first thing we're going to need is a table to store our sequence numbers. This can be fairly simple:
CREATE TABLE Invoice_Sequence (base CHAR(1) PRIMARY KEY CLUSTERED, invoiceNumber INTEGER);
In reality the
base column should be a foreign-key reference to whatever table/id defines the business(es)/entities you're issuing invoices for. In this table, you want entries to be unique per issued-entity.
Next, you want a stored proc that will take a key (
base) and spit out the next number in the sequence (
invoiceNumber). The set of keys necessary will vary (ie, some invoice numbers must contain the year or full date of issue), but the base form for this situation is as follows:
CREATE PROCEDURE Next_Invoice_Number @baseKey CHAR(1), @invoiceNumber INTEGER OUTPUT AS MERGE INTO Invoice_Sequence Stored USING (VALUES (@baseKey)) Incoming(base) ON Incoming.base = Stored.base WHEN MATCHED THEN UPDATE SET Stored.invoiceNumber = Stored.invoiceNumber + 1 WHEN NOT MATCHED BY TARGET THEN INSERT (base) VALUES(@baseKey) OUTPUT INSERTED.invoiceNumber ;;
That's right, you'll still get blocking per-business when issuing invoice numbers. You can't avoid this if invoice numbers must be sequential, with no gaps - until the row is actually committed, it might be rolled back, meaning that the invoice number wouldn't have been issued.
Now, since you don't want to have to remember to call the procedure for the entry, wrap it up in a trigger:
CREATE TRIGGER Populate_Invoice_Number ON Invoice INSTEAD OF INSERT AS DECLARE @invoiceNumber INTEGER BEGIN EXEC Next_Invoice_Number Inserted.base, @invoiceNumber OUTPUT INSERT INTO Invoice (base, invoiceNumber) VALUES (Inserted.base, @invoiceNumber) END
(obviously, you have more columns, including others that should be auto-populated - you'll need to fill them in)
...which you can then use by simply saying:
INSERT INTO Invoice (base) VALUES('A');
So what have we done? Mostly, all this work was about shrinking the number of rows locked by a transaction. Until this
INSERT is committed, there are only two rows locked:
Invoice_Sequencemaintaining the sequence number
Invoicefor the new invoice.
All other rows for a particular
base are free - they can be updated or queried at will (deleting information out of this kind of system tends to make accountants nervous). You probably need to decide what should happen when queries would normally include the pending invoice...