Can't comprehend Autonumber Fields

[jal]

A while back I finally became aware that AutoNumber columns do not necessarily provide unique values.


Today I discovered a bug in an old database. I had a query like this (note that tblCombine is initially empty at the start of this query).

INSERT INTO  tblCombine
(
SELECT * FROM table1
UNION ALL
SELECT * FROM table2
UNION ALL
SELECT * FROM table3
)

tblCombine has an AutoNumber column. The old code assumed that each row inserted by the above query would get a unique rownumber (autonumber). Didn't turn out that way, the engine generated some duplicate numbers. Hence the bug.

I tried two fixes. First, I tried adding a Unique constraint/Index to the Autonumber column. This only resulted in a runtime crash because the engine tried to insert duplicate autonumbers.

So instead I dropped the Autonumber column using "ALTER TABLE tblCombine DROP COLUMN AutoNum". Then I did the inserts. And then I re-created the column using "ALTER TABLE tblCombine ADD COLUMN AutoNum COUNTER". This time around the engine did NOT generate duplicate autonumbers.


My question is this. Why would a database engine ever generate dups for an autonum column in the first place? Doesn't that defeat the purpose? Why would a developer actually DESIRE to have an autonum column filled with numbers completely meaningless in virtue of being duplicated all over the place? Do most other database engines function this way?

Can anyone help me make sense of this?

 

[jal]

Well, I think I figured out the answer. Now I'm thinking the engine didn't generate the dup values, they must have already existed in the three source tables. I didn't remember that those tables also have an autonum column.

 

[Red17]

I would think that you must have had the autonumber Indexed property set to something other than "Yes (No Duplicates)" in the table design when you ran the append query as the database engine should have generated a warning message and not allow those dups to be written.

 

[Galaxiom]

Well, I think I figured out the answer. Now I'm thinking the engine didn't generate the dup values, they must have already existed in the three source tables. I didn't remember that those tables also have an autonum column.

Exactly. Records can be appended including the autonumber value but the engine will reject them if there are duplicates. You would need to append the other fields only and allow the new table to autonumber the field.

Note however that sometimes the autonumber seed can get corrupted and this will cause attempted duplicates. But that is not what happened for you.