Hi again

Now i have another auto-increment problem

what's the correct way of updating the AUTO-INCREMENT count by +1 (without inserting a row) so that it will skip a number ?

The reason I'm asking is that my client wants to be able to create manual orders outside the site but still keep the order/invoice numbering system

I can't find anything about how to do this in the MYSQL manual

thanks for any help you can give

PS, I thought of just inserting an empty row and getting the insert id but there must be a more elegant way of doing it ...

    I'd go with a non-auto incremented field for this.

      you mean by getting the highest existing value and then adding 1 each time ?

      It's a thought - though it would mean making changes to several blocks of code that work

      I'll have to ponder over that one ...

      thanks

        do you mean you want to do auto-incrementation but you need to start at a certain number?

        ALTER TABLE tblname AUTO_INCREMENT=value;

          I assume by this that you want to use say odd numbers within the db and even numbers for the manual invoices? Can't think of any other reason why you would want to skip a number every time.

          Forget it. In some dbs such as mssql you can set the autinc to go up by steps other than one, but not in mysql that I know of. Also, you could not insert the manual invoice numbers either. So you will be maintaining parallel sets of records.

          Basically, you are trying to reinvent standard bookkeeping practice and this is a bad idea. There are just going to be too many exceptions to the rule that each and every order always and only ever results in 1 invoice, or that each invoice only covers 1 order. How will your system cope with rejected orders, backorders, revised and repeat orders? Not too mention revised and re-issued invoices.

          Besides, if you are in the UK then current legislation dictates that all invoice numbers are unique and sequential. It may be different in your jurisdiction but I doubt it. Believe me, tax men and auditors hate gaps in invoice numbers - too many opportunities for fraud and tax evasion.

          One light in the darkness is the ability of mysql to group autoincs with another column in a multi-column index. This means that you can use a prefix with the number to indicate manual and automated invoices. It also means that failed inserts will not infringe any legal requirement for invoice numbers to be sequential as mysql will reuse the autoinc that would otherwise be burnt by a failed insert.

          So, abandon any notion that you can synchronise order numbers with invoice numbers but be glad that you can run parallel sequences of invoice numbers by using a prefix for disparate sources or types.

          Hope this helps. If your clients want to insist on the current spec, look up some legal requirements or accounting best practice to show them what a bad idea it is.

          PS I work for a large accounting practice incase you were wondering how I know all this.

            The multicolumn index approach requires a MyISAM table.

            @: Thanks for pointing that out - I didn't know that behaviour and just read it up in the manual.

            It is possible to have the autoincrement count up in other steps than +1 and to make it start at a different number by setting the server system variables autoincrement_increment and autoincrement_offset.

            The big drawback: this works serverwide for all tables.

            (and one last note: it's even possible to fiddle a record in between two autoincs by turning off and on the indices, but it is by no means safe and should be avoided)

            I do not advocate using any of what I mentioned here, except the multicolumn index, in the case described above, for all the good reasons Roger gave in his post.

            Bjom

              Write a Reply...