Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2015
    Posts
    5

    Unanswered: Auto increment field skipping numbers

    We have an auto increment field that's supposed to use sequential integer numbers, but it's occasionally missing a value. Nothing deletes from this table, and we can see that it's missing right away, so we're pretty sure it's not writing to the table, not that it's writing and being deleted. My best guess is that even if there's a problem with the data that prevents the record from writing to the database, it still increments the integer field. Is that correct?

  2. #2
    Join Date
    Feb 2012
    Posts
    133
    I think you're dealing with "identity gaps" on your table. If so, take a look at the link for dealing identity problem

    http://www.sypron.nl/idgaps_txt.html

  3. #3
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    If you use and identity column the identity gap that Makecompile mentioned is probably the culprit. If you use a custom function to increment the id's you might need to check that.
    I'm not crazy, I'm an aeroplane!

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    or better yet don't rely on auto-increment to have any meaning outside the db itself. use it solely to provide a guaranteed unique value inside the system. Don't* try and use it for things like order numbers, GRN's, Invoices etc. generate the the number yourself if you MUST have a sequential number with no gaps.


    *if you like winding up accountants / auditors and the like then DO use autoincrement, nothing hacks off an accountant, more than seeing missing numbers in a sequence
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by healdem View Post
    *if you like winding up accountants / auditors and the like then DO use autoincrement, nothing hacks off an accountant, more than seeing missing numbers in a sequence
    I'll actually take issue with that. If your auditor/accountant gets upset about gaps in your numbers, give them a copy of GAAP to read while you go find a competent auditor/accountant. The numbers assigned to invoices and other forms are called "audit controls" for a very good reason. These are critical to an auditor being able to determine if a business is complying with the appropriate procedures (which are different for different businesses because the appropriate controls depend on the type of business, regulatory oversight, etc.). If there are no numeric gaps in an audit period, the lack of the gaps ought to be an Audit Finding and a noted security/compliance threat.

    See http://documentlibrary.qad.com/docum...ans.07.03.html for the discussion of this from an audit/accounting perspective.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Jun 2015
    Posts
    5
    We don't really care about the gaps. Our problem is that another table has a foreign key pointing to this identity field, and it's failing when it tries to write a record tied to a "missing" number, which puts an error about a missing foreign key in our log file. We're not even sure if the missing data is a bad thing - it may be data that really should have been rejected, so it's not a problem. Or one guy thinks it may be multiple things trying to write to the same table at the same time and interfering with each other, but I doubt that. But the point is that I've been asked to verify what these errors in the log file are and why they're occurring, and all I have right now are guesses.

    So I'm starting by trying to understand what would cause an auto-increment in Sybase to increment the number without actually writing the record with that number to the database. Being new to Sybase (I'm mostly a Microsoft SQL Server guy), I'm just trying to wrap my head around the nuances.
    Last edited by Fromper; 08-11-15 at 12:15.

  7. #7
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    In that case you have a problem with your foreign keys. It sounds like the foreign key isn't determined properly.
    I'm not crazy, I'm an aeroplane!

  8. #8
    Join Date
    Feb 2012
    Posts
    133
    i don't see any business purpose pointing your FK to your identity field.

  9. #9
    Join Date
    Jun 2015
    Posts
    5
    Quote Originally Posted by makecompile View Post
    i don't see any business purpose pointing your FK to your identity field.
    The identity field is part of the primary key of the parent table. We want every child table record tied to a unique record in the parent table, so the parent table's primary key is a foreign key for the child table.

  10. #10
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Quote Originally Posted by Fromper View Post
    The identity field is part of the primary key of the parent table. We want every child table record tied to a unique record in the parent table, so the parent table's primary key is a foreign key for the child table.
    Apparently your application fails to determine the right foreign key value. You should have a developer look into that.
    I'm not crazy, I'm an aeroplane!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •