1

I want to generate Bill No From mySQL Auto Increment. I have column ID (Primary Key & Auto Increment). Now From This column ID I am generating Bill No As requested by user. Problem is If I delete some record in between then It should not skip the deleted ID when new record gets entered.

e.g. 1001,1002,1003,1004 etc In this if I delete record Id 1002 & next time If I enter any new record then How can I assign 1002 back to the new record.

SUN
  • 973
  • 14
  • 38

2 Answers2

5

It should not skip the deleted ID when new record gets entered

Yes it should. You're just relying on the system to do something that it was never designed to do and never claimed to do.

AUTOINCREMENT is not designed to generate your "Bill No". It's designed to generate an ever-incrementing identifier and guarantee uniqueness by never re-using a value. And it's doing exactly that.

You can manually generate your "Bill No" using whatever logic you like and store it in another column. It would be strange to re-use values though. Imagine if you issue a bill to someone, later delete the record, and then issue another bill to someone else with the same number.

Uniqueness is important with identifiers.

David
  • 208,112
  • 36
  • 198
  • 279
  • Great explanation. A lot of ppl do not know why databases is using auto_increment and how important this is. – Noob Aug 16 '17 at 11:24
  • Yes I agree that Auto Increment is made only for unique data. What I mean was I am not relied completely on ID to generate bill no. I have apply id prefix logic already to generate invoice no. However I also want to append ID column value with my bill no. In our industry we need to keep record of each and every number. If any number gets skip in between after performing delete then we get penalized for that. Can you suggest me what logic should I apply then if it's not possible. – SUN Aug 16 '17 at 11:56
  • @SUN: If your industry has custom requirements then you'll need to apply custom logic to generate your bill number. Maybe some kind of function which looks through existing records and finds the first empty number? Any approach is going to be less than ideal in terms of performance. I suspect the industry requirement is better served by simply not deleting records. That's *probably* the actual intent of the industry requirement. If a record is issued in error, issue a second one correcting it. Don't delete them. – David Aug 16 '17 at 11:58
  • 1
    I know I got down voted for pursuing this line in my answer, but from a financial audit trail perspective deleting invoices raised in error is a bad idea. As @David says in his comment, it would be better to raise a contra credit and then reissue the invoice. In that way your records are seamless. – Lew Perren Aug 16 '17 at 12:05
1

From a financial control point of view being able to delete and reuse invoice numbers damages your audit trail and leaves you open to risk. Also from a database integrity point of view, reusing primary keys is not allowed as it would lead to unexpected results,such as records in other tables appearing wrongly in a join. There are consequences to naturalising your primary keys (excellent discussion of natural and surrogate primary keys). The database is protecting its referential integrity by not allowing the reuse of the primary key. Indeed, as @David says in his response, part of the function of autoincrement on a primary key is to maintain the uniqueness of the key.

Now to solutions, from a financial aspect invoices should never be deleted so there is an audit trail, an incorrect invoice should have a contra through a credit note. On the database side if you must 'delete' a record and reuse the invoice number, I would suggest having a different field for the invoice number, not actually deleting, but using a field to flag active or deleted, and maintaining a separate incrementing invoice number. Although not advised, this will at least help maintain the integrity of your records.

Lew Perren
  • 1,209
  • 1
  • 10
  • 14