CODEWITHSUNDEEP
javamysqlpersistenceauto-increment
Vova
Vova

Reputation: 160

Practical issue with MYSQL auto incremented primary keys

I have written an accounting module for my java app recently.

The module is based on MYSQL tables and INNODB Engine. one of the requirements is of course a "running" ID for each invoice. I tried to generate the id using the auto_increment method and by using a sequence table, but in both I have the same problem: since the id is generated when I persist the invoice entity, if an error occurs during flushing of the persisted entity to the database, the auto_id is incremented, causing a "hole" in my series of invoices. I can of course drop this method and assign the new invoice with the maximum ID of previous invoices + 1, but I think this is a bad practice. What other methods can I use to ensure I don't have holes in my invoices series, assuming that once in a while an invoice can fail to save due to some validation issue.

Upvotes: 1

Views: 860

Answers (3)

Cylindric
Cylindric

Reputation: 5894

It's not bad practice in itself, but it might be worth having a different "traditional" numeric primary-key field to guarantee structural integrity of the tables, and have another field for the invoice number.

You can then populate that invoice number using different logic, either just a simple MAX+1 or perhaps by looking up from a keys table, to allow for different numbering sequences for different types of invoice.

For example:

CREATE TABLE `keys` (
    `id` INT NOT NULL auto_increment,
    `type` VARCHAR(10) NOT NULL,
    `prefix` VARCHAR(10) NOT NULL,
    `value` INT(10) NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`)
);

INSERT INTO `keys` (`type`, `prefix`) VALUES
('Sales Receipt', 'SRI'),
('Sales Invoice', 'SIN'),
('Sales Refund', 'SRF');

Then in your (pseudo)-code you can do

Database.BeginTransaction;
NewInvNum = Database.Query("SELECT `value` FROM `keys` WHERE `type` = 'SIN'");
MyInvoice.InvoiceNumber = NewInvNum;
Database.SaveInvoice(MyInvoice);
Database.Query("UPDATE `keys` SET `value` = {0} WHERE `type` = 'SIN'", NewInvNum+1);
Database.CommitTransaction;

The transaction (or some other concurrency-protection anyway) is important, so that when more than one invoice is in the process of being created, they don't get the same number.

Upvotes: 0

Alex Stybaev
Alex Stybaev

Reputation: 4693

if your MySQL version > 5.0.2 then you could try to use triggers which will increment the value of the table column properly. But you should remember that delegating to the database the generation of invoice number, which is more related to business logic, I presume, is not a very good idea, because in failure case you may have some problems. So I'd rather advice you to generate it programmatically in your code.

Upvotes: 0

Edwin Bautista
Edwin Bautista

Reputation: 364

In spring framework there is something like 

@Transactional(rollbackFor=RuntimeException.class)

so everything should be rollback should there be problem with the server call. I'm sure other frameworks has similar approach.

Upvotes: 1

Related Questions