I don’t think any DBMS will guarantee it. There are conflicting concerns depending on the circumstances.
I was looking at this with SQLite recently. There are various caveats for it’s use in this case. If you use autoincrement it picks the one larger integer than the largest one that has been generated so far. This would work for your use case except when there is an error or deletion. So if you generate a record for insert and the insert fails due to some kind of error then the next insert will use an integer one larger than the last generated and that will leave a gap since the previous number didn’t actually insert. With autoincrement numbers are not reused so that’s good, with primary key they might be, which would be bad.
I think this is going to be very dependent on your specific DBMS and also your application logic. I think there are going to be issues with any DBMS for this type of special use case.
Over the past 25 years I’ve worked most of the major databases out there and I can’t remember a single case where an auto generated gaps in the sequence. But that is not my point, such keys sometimes have to be reset when you make alterations to the database and so on. And thus they are not suitable for implementing business rules in the direct manner suggested.
I think you won’t get gaps in autoincrement sql databases unless you delete previous records.
if you forbid to delete records, then you won’t get any gap.
there is a problem if you dont check unique-autoincrement-primary key in a table
then if you vacuum the database, you can get mix in the rowids
but
if you make the rowid as unique-autoincrement-primary key in each table
and forbid to delete records
then you won’t get any gap in your numbers.
Not necessarily delete causes gaps. If you have 2 processes (p1 an p2) acquiring 2 sequences (same ID, let’s say p1 gets id1, and p2 id2 later) and p2 finishes its process and update some record with id2, and p1 is canceled because “whatever”, the record ids will miss id=1. It’s normal. If you MUST guarantee that all numbers are used, Its your job to handle and avoid gaps.
Well to start with a new auto key is only generated when a record is inserted into the database not when records are updated, so your example is not valid! I think we are really stretching it now, one of the system I worked on had around 1,500 traders generating approx. 1.5m transactions per day and I never heard of an issue where the auto inc fields failed.
Not sure what DB you are talking about, but surely you are not aware of DB sequences used by DBs like Oracle, PostGreSQL, Firebird and others. Sometimes people ask to the DB “get me a new ‘order’ number” and show it in the screen with a form that people complete and save… Or cancel. The number is gone, and who cares? If I do care I create my own function to avoid gaps or any other necessary legal task.
Sorry to jump into this one late but in the multiple accounting systems I’ve worked on there a difference between record ID for the invoice and the Invoice Number. The RecordID is database supplied. The Invoice Number is usually recommended (tho most people don’t change it) and in some cases correspond to a preprinted form. In todays electronic world preprinted forms aren’t common but don’t rule it out.
Now, in the US it’s fairly common for Invoice Numbers to be manipulated manually. If Joe orders a product that ends up being Invoice# 100101 and then calls up and informs customer service that we really wanted to make line Item 1 a quantity of 11 rather than 10, the clerk will often call that invoice 100101a. Technically a new invoice number but the record stays the same (an audit trail tracks the change) and everyone in shipping knows that Invoice 100101a is a changed Invoice. However, 100101 and 100101a, can never be reused again.
Different rules for different folks so don’t skewer me. I’m just telling you what several big commercial, public, accounting apps do in the US.
Regardless, I would NOT use RecordID and InvoiceNumber interchangeably. If you use them that way you are forever screwed if you change databases and have to export/import data. Some db’s you can override the primary key on import but you probably shouldn’t (go ahead and debate on that). There’s a difference between the internal data and what the end user sees. To keep the referential integrity you really don’t want to mess with the database auto-id’s if you can help it.
