Appending to Autonumber Field

[tcb]

If I have a table with an autonumber as primary key which I would
depoplulate and then repoplulate by appending from another table with
the same structure, will it always work? i.e, record 1 autonumber =
1, record 2 autonumber = 2... etc. It works in my testing, but it
doesn't seem like it should, or that I should count on it, nor does it
seem like a good practice.

For example, if there are 25 (1-25) retailers in tRetailer and if I
"Delete * FROM tRetailer", I know that if I manually entered another
retailer the next Autonumber would be 26.

However, if I append from the other table, my test indicated that it
will accept 1,2,3... ("INSERT INTO tRetailer SELECT tNewRetailer.*
FROM tNewRetailer"

***

Interestingly, and this seems like an anomaly, when the autonumber was
not a primary key, in a test I ran the same append twice. It
populated duplicate numbers into the autonumber field.

 

[Douglas J. Steele]

What you're describing is indeed how Autonumbers work.

The fact that you care about the value of the Autonumber, though, suggests
that you probably shouldn't be using them. Autonumbers exist for one purpose
only: to provide a (practically guaranteed) unique value that can be used as
a primary key. The values 3, 6, 11 serve that purpose just as well as 1, 2,
3. Most of us don't even bother showing the value of the Autonumber field to
our users.

The reason why the Autonumber field that wasn't part of the table's primary
key accepted the same value twice is that Autonumbers are just Long Integers
that get generated. By and of themselves, there's no need for them to be
unique.

 

[David W. Fenton]

The reason why the Autonumber field that wasn't part of the
table's primary key accepted the same value twice is that
Autonumbers are just Long Integers that get generated. By and of
themselves, there's no need for them to be unique.

An Autonumber is a Long Integer field with a special kind of default
value. That is all.

Uniqueness is a function of indexing, not of the Autonumber field.

True, default values will be unique, but without the unique index,
you can't prevent the appending of duplicate values. However, given
that Autonumbers are mostly used for PKs, there is going to be a
unique index to prevent the appending of duplicate values.

 

[Douglas J. Steele]

An Autonumber is a Long Integer field with a special kind of default
value. That is all.

Uniqueness is a function of indexing, not of the Autonumber field.

True, default values will be unique, but without the unique index,
you can't prevent the appending of duplicate values. However, given
that Autonumbers are mostly used for PKs, there is going to be a
unique index to prevent the appending of duplicate values.

Yup, that's exactly what I was trying to say.