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.

your friend should get out more

there is no issue

Well, that's one thing that's true

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.

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)

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.

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.

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.

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.

"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...

i didn't understand what you meant here

or here

care to elaborate?

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?

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.'

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.

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

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.