Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2005

    Red face Unanswered: Autoincrement field

    I have table having id which is primary,notnull & Autoincrement & name field. It default value is 1.
    I inserted 4 record so the id became 4.Now I delete 4th record & then again insert a row.Now instead of getting id=4 I get id=5 skipping the 4th id.
    Can any body help me out


  2. #2
    Join Date
    Dec 2005

    sybase identity

    Sybase identity works that way. It can skip some numbers too. What is happenning with you is normal behaviour. You will have to write your own routine to do a (prev + 1) to get perfectly sequential numbers. A trigger might be more appropriate for this.


  3. #3
    Join Date
    Jan 2003
    Geneva, Switzerland
    This is perfectly normal.

    What behavior would you expect if you have rows 1 through 10000, and you delete row with id 10. Should the next inserted value use the id 10?

    As a general design rule - if you are using surrogate primary keys (i.e. id numbers as primary keys) then the ID values should NOT have any meaning beyond identifying the correct row. In particular there should not be any assumption that the ID values are sequential, or strictly increasing, etc.


  4. #4
    Join Date
    Feb 2004
    If u r inserting one row at a time. I am wondering if u can use the below set option :

    set identity_insert <table name> on
    and then manully insert row for id=4
    and again set identity_insert <table name> off.

    So now from the next time whenever u insert it will take id=5.


Posting Permissions

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