Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Apr 2010
    Posts
    24

    Auto increment ID as Primary key

    Ok, so I have a bunch of questions stored in a table, and I used Ques_ID as the auto-incremented primary key. A friend then warned me that if I deleted a question in the middle, it might cause some issues, and he was right. If I remove question #3, it doesn't change the number to reflect that so it goes from #2 to #4. And to top it off, when I add a #5, it stores it where #3 was. Has anybody ever experienced this issue and found a solution? Or maybe you determined it wasn't an issue at all. Any opinions would be appreciated.
    DevilsAdvocate
    ------------------------
    The elephant in the room
    ------------------------
    Ordo ab chao

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your friend should get out more

    there is no issue
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2010
    Posts
    24
    Quote Originally Posted by r937 View Post
    your friend should get out more
    Well, that's one thing that's true

    Quote Originally Posted by r937 View Post
    there is no issue
    I tested it. It is a problem. Well, potentially at least. I added 4 questions. I then deleted ID #3. It then read ID 1,2,4. So I added a new one and let the autoincrement take care of its number and it numbered it 5 and stuck it between 2 and 4, so it read ID 1,2,5,4.

    My question is: what if you're iterating through a table or something looking for the first 5 ID's. It would throw them in the stored order which is 1,2,5,4,6which could become a problem couldn't it? Do you get what I mean? I hope I'm explaining that ok.
    DevilsAdvocate
    ------------------------
    The elephant in the room
    ------------------------
    Ordo ab chao

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ur doing it wrong

    there is no sequence to the rows in a table -- they are stored wherever the engine wants to put them, and they are ~not~ inserted where there is a "gap"

    the gaps do not matter, full stop

    the only sequence you will ever get is when you use an ORDER BY clause in your query

    you want the lowest 5 ids?

    simple --

    SELECT id FROM daTable ORDER BY id LIMIT 5
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2010
    Posts
    24
    Quote Originally Posted by r937 View Post
    ur doing it wrong

    there is no sequence to the rows in a table -- they are stored wherever the engine wants to put them, and they are ~not~ inserted where there is a "gap"

    the gaps do not matter, full stop

    the only sequence you will ever get is when you use an ORDER BY clause in your query

    you want the lowest 5 ids?

    simple --

    SELECT id FROM daTable ORDER BY id LIMIT 5
    So it won't matter? As long as the ID remains the same , I can pull out as many as I want before and it will still find it based on the ID no matter where it is stored in chronological order in the table? (Not arguing, clarifying)
    DevilsAdvocate
    ------------------------
    The elephant in the room
    ------------------------
    Ordo ab chao

  6. #6
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    The sole purpose of an autonumber field (aka Sequence or Identity) is to provide a number that is guaranteed to be unique. That's all. It's typically used in cases where you wish to use a surrogate primary key, rather than a natural key, in your table.

    Unless you specifically provide the order in which you wish data to be presented, no database is guaranteed to present the data in any particular order. (the ORDER BY clause in a select statement is needed)

    If your needs include the requirement that a set of records be presented in a particular order which may be different than the order of your system's autonumber field, then you would need a field in your table to be used for ordering/sequencing of the results.


    i.e. If you want to have a specific order of presentation that is independent of the order in which a record may be added, you need to 'roll your own' ordering mechanism.
    Last edited by loquin; 04-15-10 at 17:48.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by loquin View Post
    The sole purpose of an autonumber field (aka Sequence or Identity) is to provide a number that is guaranteed to be unique.
    I don't want to take the thread off course but this isn't true. I can't speak for all engines but for the ones I am familiar with there is no constraint attached to an identity or autonumber column. You can have unlimited rows in a SQL Server or Access table all with the same value in the identity or autonnumber column.

    I couldn't leave this unchallenged because it is a commonly held misconception that identities are inherently unique.

  8. #8
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    You are correct, in that the enforcement of uniqueness must come from the constraint on the field. However, unless you reset the identity, (or it wraps around,) the number it provides is unique in the set of numbers generated by the identity. The same is true of sequences.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Or insert values explicitly.

    I thought you would be aware of this. It is one of my pet hates (and sadly very common in the Access forum) when primary keys and autonumbers are considered synonymous.

  10. #10
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    "with great power, comes great responsibility, grasshopper..."

    You actually have to USE the sequence/identity to reap the benefits. And, unless you write a trigger to drop any autonumber field's value before it is inserted, you can't FORCE database servers to only use the identity/sequence value...
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by loquin View Post
    You actually have to USE the sequence/identity to reap the benefits.
    i didn't understand what you meant here

    Quote Originally Posted by loquin View Post
    And, unless you write a trigger to drop any autonumber field's value before it is inserted, you can't FORCE database servers to only use the identity/sequence value...
    or here

    care to elaborate?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Apr 2010
    Posts
    24
    Quote Originally Posted by loquin View Post
    The sole purpose of an autonumber field (aka Sequence or Identity) is to provide a number that is guaranteed to be unique. That's all. It's typically used in cases where you wish to use a surrogate primary key, rather than a natural key, in your table.

    Unless you specifically provide the order in which you wish data to be presented, no database is guaranteed to present the data in any particular order. (the ORDER BY clause in a select statement is needed)

    If your needs include the requirement that a set of records be presented in a particular order which may be different than the order of your system's autonumber field, then you would need a field in your table to be used for ordering/sequencing of the results.


    i.e. If you want to have a specific order of presentation that is independent of the order in which a record may be added, you need to 'roll your own' ordering mechanism.
    That's all I want it for, to query questions and answers to display them.
    The question ID is the question number, at least in theory, so would that be a problem if I wanted to show 1-5? Is my understanding correct?
    DevilsAdvocate
    ------------------------
    The elephant in the room
    ------------------------
    Ordo ab chao

  13. #13
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Quote Originally Posted by r937 View Post
    i didn't understand what you meant here
    Quote Originally Posted by loquin
    You actually have to USE the sequence/identity to reap the benefits.
    What I meant was this: If you insert explicit values into the field, thus overriding the default identity/sequence, you aren't actually using the identity/sequence value. It was in response to poot's reply 'Or insert values explicitly.'

    Quote Originally Posted by r937 View Post
    or here
    Quote Originally Posted by loquin
    And, unless you write a trigger to drop any autonumber field's value before it is inserted, you can't FORCE database servers to only use the identity/sequence value...
    care to elaborate?
    yeah - that was a bit ... muddy. When you insert/update, you can supply a value to the ID field. The value you supply value will override the default value of the sequence/identity.) AFAIK, there's no way to specify that the server will only use the identity/sequence, other than writing a before trigger to set the explicitly supplied [new] value to null, allowing the default constraint to be applied.
    Last edited by loquin; 04-16-10 at 14:05.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by DevilsAdvocate View Post
    Is my understanding correct?
    it certainly is

    therefore you should not be using auto_increment for the question number -- rather, you should assign the question number value manually as you add each new question to the table

    and of course it would still be your primary key

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Quote Originally Posted by DevilsAdvocate View Post
    That's all I want it for, to query questions and answers to display them.
    The question ID is the question number, at least in theory, so would that be a problem if I wanted to show 1-5? Is my understanding correct?
    So, since any automatically generated field could have gaps, you would either explicitly supply the question id, or keep the question number in a separate, distinct field from any identity/sequence/autonumber field. And, order by the question number when you select data from the table.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

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