By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,811 Members | 785 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,811 IT Pros & Developers. It's quick & easy.

Restart Autonumber

P: n/a
If I have a table with an autonumber primary key and 100 records and I delete
the last 50 records, the next record added would have a primary key of 101. Is
there any way to have the primary key start at 51 after the last 50 records are
deleted?

Thanks!

Traci
Nov 13 '05 #1
Share this Question
Share on Google+
35 Replies


P: n/a
That's the way autonumbers are. In fact, you're lucky that the numbers
currently correspond to the record number: had you started to add a record
and then decided not to save it, you would have lost that number. Also, if
you were to use Access Replication, be aware that the autonumber values
would no longer be sequential, but random.

Autonumber fields really aren't intended for "human consumption": their only
intent is to provide a (practically guaranteed) unique value that can be
used as a primary key, and they fill that purpose with or without gaps.

If, after reading all this, you still want to try and use autonumbers and
have their value mean something, you can compact the database after doing
the delete, and the next autonumber assigned should be one more than the
last one used.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Traci" <tm******@earthlink.net> wrote in message
news:BW*****************@newsread2.news.atl.earthl ink.net...
If I have a table with an autonumber primary key and 100 records and I delete the last 50 records, the next record added would have a primary key of 101. Is there any way to have the primary key start at 51 after the last 50 records are deleted?

Thanks!

Traci

Nov 13 '05 #2

P: n/a
Sal
Just compact database, next number will be 51

Slawomir Piascik

"Traci" <tm******@earthlink.net> wrote in message
news:BW*****************@newsread2.news.atl.earthl ink.net...
If I have a table with an autonumber primary key and 100 records and I delete the last 50 records, the next record added would have a primary key of 101. Is there any way to have the primary key start at 51 after the last 50 records are deleted?

Thanks!

Traci

Nov 13 '05 #3

P: n/a
Actually, This will not recover the autonumbers. It will pick up right from
the last autonumber before the delete.

--
Reggie

----------
"Sal" <sl*****@bellatlantic.net> wrote in message
news:Nu*****************@nwrdny01.gnilink.net...
Just compact database, next number will be 51

Slawomir Piascik

"Traci" <tm******@earthlink.net> wrote in message
news:BW*****************@newsread2.news.atl.earthl ink.net...
If I have a table with an autonumber primary key and 100 records and I

delete
the last 50 records, the next record added would have a primary key of

101. Is
there any way to have the primary key start at 51 after the last 50

records are
deleted?

Thanks!

Traci


Nov 13 '05 #4

P: n/a
Traci

In addition to the cautions noted by Doug in his response, be aware that if
your table's primary key (?your Autonumber) is used as a foreign key in
one/more child tables, "restarting" your Autonumbers could hose the
relationship between parent and child records.

--
Good luck

Jeff Boyce
<Access MVP>

Nov 13 '05 #5

P: n/a
I have been reading many posts by users that claim to have years of
experience with Access that promote the use of Autonumber generated
values as primary keys. Don't get me wrong; I use Autonumber values as
primary keys for standalone tables, such as those that really do not
have a primary key unless you use multiple key fields. But I keep
asking myself why anyone would create an architecture of two related
tables upon which the relationship is based on an Autonumber key
field. I'm currently working on one right now where I'm converting an
"Orders" table that uses Autonumber to generate Order numbers. This
Autogenerated value is then stored in the OrderDetails records. If the
scenario that you describe below occurs, guess what? You've just lost
your relationship between the two fields, because you cannot enter
your own values in an Autonumber field, and your OrderDetails table is
useless because the reference to the Orders table is gone. The
OrderDetails table will use an Autonumber key to basically serve as a
surrogate primary key, but I use a "natural" key that I control -
which in this case is my own "rolled" invoice #. The database I'm
converting is going to use my own "rolled" method, upon which a
function will return an incremented value from a table. With the
experience I've had with tables related based on Autonumbers, I will
never willingly design one to be as such.
"Jeff Boyce" <Je**********@msn.com-DISCARD_HYPHEN_TO_END> wrote in message news:<O8**************@TK2MSFTNGP09.phx.gbl>...
Traci

In addition to the cautions noted by Doug in his response, be aware that if
your table's primary key (?your Autonumber) is used as a foreign key in
one/more child tables, "restarting" your Autonumbers could hose the
relationship between parent and child records.

Nov 13 '05 #6

P: n/a
On 16 Jul 2004 21:31:03 -0700, dr*********@hotmail.com (James) wrote:
I have been reading many posts by users that claim to have years of
experience with Access that promote the use of Autonumber generated
values as primary keys. Don't get me wrong; I use Autonumber values as
primary keys for standalone tables, such as those that really do not
have a primary key unless you use multiple key fields. But I keep
asking myself why anyone would create an architecture of two related
tables upon which the relationship is based on an Autonumber key
field.
Because there is absolutely no way that you can define a natural key
that will never change, nor one that is guaranteed to be unique
throughout all of time. Autonumbers are both as long as the
appropriate RI have been established. At least they are supposed to
be - there was a bug in Access 2000 I believe, that has since been
fixed, where autonumbers were duplicated, but that was a bug.
I'm currently working on one right now where I'm converting an
"Orders" table that uses Autonumber to generate Order numbers. This
Autogenerated value is then stored in the OrderDetails records. If the
scenario that you describe below occurs, guess what? You've just lost
your relationship between the two fields, because you cannot enter
your own values in an Autonumber field, and your OrderDetails table is
useless because the reference to the Orders table is gone. The
OrderDetails table will use an Autonumber key to basically serve as a
surrogate primary key, but I use a "natural" key that I control -
which in this case is my own "rolled" invoice #. The database I'm
converting is going to use my own "rolled" method, upon which a
function will return an incremented value from a table. With the
experience I've had with tables related based on Autonumbers, I will
never willingly design one to be as such.
There have been many threads over the years on this issue. People
line up on one side or the other and have strong feelings about the
mental acuity of those that feel differently. ;-)

But either system works as long as there are enough safeguards built
into the programming. It just takes fewer safeguards if an autonumber
is used, IMO.

mike

"Jeff Boyce" <Je**********@msn.com-DISCARD_HYPHEN_TO_END> wrote in message news:<O8**************@TK2MSFTNGP09.phx.gbl>...
Traci

In addition to the cautions noted by Doug in his response, be aware that if
your table's primary key (?your Autonumber) is used as a foreign key in
one/more child tables, "restarting" your Autonumbers could hose the
relationship between parent and child records.


Nov 13 '05 #7

P: n/a
James

Not sure how this relates to what I interpreted as the original point -- how
can I restart my numbering system? My caution was that any time you
re-initiate a primary key sequence (whether an Autonumber or a "hand-rolled"
custom ID function), you risk orphaning "child" table rows (at best), or
causing totally unrelated child table rows to be connected to the wrong
"parent".

And if there are "child" records, neither an Autonumber or a "hand rolled"
ID function should resequence to "fill in" for missing values in the
sequence. Unless, of course, you have the additional procedures to modify
ALL the child records!

The topic of "natural" keys vs. arbitrary, unique IDs does show up a lot --
and I suspect the discussions border on "religious beliefs". But I'm not
sure that a custom-built procedure that generates a sequential, unique
identifier could be consider a "natural" key.

--
Thanks for the thoughts

Jeff Boyce
<Access MVP>

Nov 13 '05 #8

P: n/a
In message <63*************************@posting.google.com> , James
<dr*********@hotmail.com> writes
I have been reading many posts by users that claim to have years of
experience with Access that promote the use of Autonumber generated
values as primary keys. Don't get me wrong; I use Autonumber values as
primary keys for standalone tables, such as those that really do not
have a primary key unless you use multiple key fields. But I keep
asking myself why anyone would create an architecture of two related
tables upon which the relationship is based on an Autonumber key
field.
What you have to bear in mind is that there's a difference between a
table and an entity. Entities are in the logical data structure and
tables are in the physical data structure.

Whenever there's a difference between the logical and physical data
structures there is a risk of data corruption.

Joe Celko says that autonumbers don't exist in the logical data
structure, I disagree. I think that autonumbers appear in the LDS as
separate entities with a 1:1 relationship to another entity. The problem
is that that 1:1 relationship isn't inherent in the data, it has to be
enforced by the programmer. If that isn't done then you get situations
where, for instance, a customer's order is entered twice and gets two
different autonumber "Order Numbers."

For that reason it's safest to use a natural key if there is one
available. Adding an autonumber doesn't create a natural key of there
isn't already one in the real data. If there is one already then the
autonumber is probably redundant.

There are times when you need to denormalise the table for performance
reasons, and that may involve adding an autonumber. With current
database systems you are less likely to need this, storage is so cheap
that it's often better to duplicate key fields (even compound keys) than
to add an autonumber.

The most difficult situations arise when you deal with data about
people. There is no simple and easy to use natural key for people. Over
in comp.databases.theory I once suggested that the precise latitude,
longitude, altitude and time of birth would make a good natural key if
we could persuade people to record it.

I'm currently working on one right now where I'm converting an
"Orders" table that uses Autonumber to generate Order numbers. This
Autogenerated value is then stored in the OrderDetails records. If the
scenario that you describe below occurs, guess what? You've just lost
your relationship between the two fields, because you cannot enter
your own values in an Autonumber field, and your OrderDetails table is
useless because the reference to the Orders table is gone.


That's called an update anomaly, and it's expected when you work with
denormalised data. It's up to the programmers to build system where that
type of anomaly can never occur.

There's also an insert anomaly when the same order gets entered twice.
Again, it's up to the designers to build systems where that can't
happen.

So my advice is take a good long look at the logical data structure and
try very hard to find a natural key. Use that if you can, and only use
autonumbers if you are forced to. There are two reasons for doing that.

The first is performance, to avoid having to match long key fields or to
avoid storing long fields both as primary key and as foreign keys.
Modern systems mean that these cause less problems than they once did.

The seconds is that sometimes you don't have a natural key available,
such as when you are dealing with data about people. At these times you
just have to accept that your data is likely to have errors in it, and
there are limits to what you can do about it. You can choose to use
someone else's autonumber field (for instance a social security number)
or create your own. When you create your own then you need to put
procedures in place to make sure that there is always an exact 1:1
relationship between your autonumber field and the people it is supposed
to identify. You can't do that in software.


--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.

Nov 13 '05 #9

P: n/a
James wrote:
I have been reading many posts by users that claim to have years of
experience with Access that promote the use of Autonumber generated
values as primary keys. Don't get me wrong; I use Autonumber values as
primary keys for standalone tables, such as those that really do not
have a primary key unless you use multiple key fields. But I keep
asking myself why anyone would create an architecture of two related
tables upon which the relationship is based on an Autonumber key
field. I'm currently working on one right now where I'm converting an
"Orders" table that uses Autonumber to generate Order numbers. This
Autogenerated value is then stored in the OrderDetails records. If the
scenario that you describe below occurs, guess what? You've just lost
your relationship between the two fields, because you cannot enter
your own values in an Autonumber field, and your OrderDetails table is
useless because the reference to the Orders table is gone.


There's nothing wrong and most things right with Autonumber as a primary
key. The mistake is when people try to do the thing you're talking
about. Once a primary key has been set, it should NEVER change, short
of some sort of massive system-wide upgrade (and then rarely).

Nov 13 '05 #10

P: n/a
> Because there is absolutely no way that you can define a natural key
that will never change, nor one that is guaranteed to be unique
throughout all of time. Autonumbers are both as long as the
appropriate RI have been established. At least they are supposed to
be - there was a bug in Access 2000 I believe, that has since been
fixed, where autonumbers were duplicated, but that was a bug.


Is there really anything in life that is guaranteed to NOT change? I
supposed there are a few things, like the race of a person. You might
think social security #'s, but people even receive new of those from
time to time. ;-)

My IT manager at work even goes so far as to say that PrimaryKeys
should be totally meaningless in a table; in other words, if your PK
is made up of any part of your data, then you are not using a correct
PK. I had to kindly disagree with him ;)
Nov 13 '05 #11

P: n/a
>> Joe Celko says that autonumbers don't exist in the logical data
structure, I disagree. I think that autonumbers appear in the LDS as
separate entities with a 1:1 relationship to another entity. >>

I am living in Salt Lake City, so LDS has another meaning to me :)
The problem is that that 1:1 relationship isn't inherent in the data, it has to be enforced by the programmer. <<

Exactly right: autonumberings is not in the data. It is created
outside the reality of the data model in the PHYSICAL storage;
unverifiable, random, irreproducible and proprietary. Would you take
a drug that uses that kind of "science"? No. So why do you run
databases like that?
you get situations where, for instance, a customer's order is entered twice and gets two different autonumber "Order Numbers." <<

It is worse if there is a real key; one UPDATE uses the autonumber and
another uses the real key. If I were using two reald keys -- say,
Dewey Decimal and Library of Congress in a library -- I can
co-ordinate them. But with a phyasical locator, I am screwed.
I once suggested that the precise latitude, longitude, altitude and

time of birth would make a good natural key if we could persuade
people to record it.<<

That (location and birthdate) is the Swedish nation id number!
Nov 13 '05 #12

P: n/a
On 17 Jul 2004 10:27:05 -0700, dr*********@hotmail.com (James) wrote:
Is there really anything in life that is guaranteed to NOT change? I
supposed there are a few things, like the race of a person.


In practice "race" is an administrative classification and therefore
subject to change.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
Nov 13 '05 #13

P: n/a
On 17 Jul 2004 10:27:05 -0700, dr*********@hotmail.com (James) wrote:
Because there is absolutely no way that you can define a natural key
that will never change, nor one that is guaranteed to be unique
throughout all of time. Autonumbers are both as long as the
appropriate RI have been established. At least they are supposed to
be - there was a bug in Access 2000 I believe, that has since been
fixed, where autonumbers were duplicated, but that was a bug.
Is there really anything in life that is guaranteed to NOT change?


No, there isn't. Thanks for making the point for me. ;-)

Isupposed there are a few things, like the race of a person. You might
think social security #'s, but people even receive new of those from
time to time. ;-)

My IT manager at work even goes so far as to say that PrimaryKeys
should be totally meaningless in a table; in other words, if your PK
is made up of any part of your data, then you are not using a correct
PK. I had to kindly disagree with him ;)


And you did so on what grounds again?

mike
Nov 13 '05 #14

P: n/a
In message <18**************************@posting.google.com >, --CELKO--
<jc*******@earthlink.net> writes
Joe Celko says that autonumbers don't exist in the logical datastructure, I disagree. I think that autonumbers appear in the LDS as
separate entities with a 1:1 relationship to another entity. >>

I am living in Salt Lake City, so LDS has another meaning to me :)
I did think of that when I wrote it.
The problem is that that 1:1 relationship isn't inherent in thedata, it has to be enforced by the programmer. <<

Exactly right: autonumberings is not in the data. It is created
outside the reality of the data model in the PHYSICAL storage;
unverifiable, random, irreproducible and proprietary. Would you take
a drug that uses that kind of "science"? No. So why do you run
databases like that?
you get situations where, for instance, a customer's order isentered twice and gets two different autonumber "Order Numbers." <<

It is worse if there is a real key; one UPDATE uses the autonumber and
another uses the real key. If I were using two reald keys -- say,
Dewey Decimal and Library of Congress in a library -- I can
co-ordinate them. But with a phyasical locator, I am screwed.
It's quite possible for there to be two natural keys in an entity, but
neither the Dewey nor LoC codes are natural. They are both examples of
autonumbers that have become visible outside the systems that created
them.

In an entity where there are two or more natural keys it is unlikely
that either could change independently of the other, because either key
uniquely identifies an object. If a key changes then the object it
identifies changes.

I once suggested that the precise latitude, longitude, altitude and

time of birth would make a good natural key if we could persuade
people to record it.<<

That (location and birthdate) is the Swedish nation id number!


They are part of the data that is used to generate the British
equivalent of the US social security number (SSN), it's called the
National Insurance number or NINO. There have been a few incidents where
people with the same name have been born in the same place on the same
day, and have been given identical NINOs.

That illustrates a point about autonumbers. If you trust the autonumber
issued by some third party you can use them as if they were a natural
key. Some American organisations have used the SSN as a primary key in a
table and discovered that it's not quite good enough to be trusted as if
it really were a natural key.

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.

Nov 13 '05 #15

P: n/a
Bernard, we're in Humpty-Dumpty country here. You're using the term
"autonumber" in a quite different sense from other people.

For the rest of us, an "autonumber" is a unique value _that the DBMS
itself assigns_ to a _record_ when the record is created, typically
though not necessarily sequential or random, but in any event unrelated
to other attributes of the entity represented by the record.

Attributes such as SSN, ISBN, Dewey or LoC "numbers" are _not_
autonumbers in this usual sense. First, the mechanism by which they are
assigned is outside the DBMS. Second - and in consequence - they are
assigned to the _entity_ (the person, book, subject or whatever), not to
the record representing it. IOW for the purposes of the DBMS they are
natural attributes.

Third, Dewey, LoC and ISBN - I don't know about SSNs - are meaningful
"smart keys" which can be parsed to obtain values of various attributes
of the entity. Generating or assigning subject classifications such as
Dewey and LoC is ultimately a matter of librarians' judgment.

On Sun, 18 Jul 2004 15:16:06 GMT, Bernard Peek <ba*@shrdlu.com> wrote:
In message <18**************************@posting.google.com >, --CELKO--
<jc*******@earthlink.net> writes
Joe Celko says that autonumbers don't exist in the logical data

structure, I disagree. I think that autonumbers appear in the LDS as
separate entities with a 1:1 relationship to another entity. >>

I am living in Salt Lake City, so LDS has another meaning to me :)


I did think of that when I wrote it.
The problem is that that 1:1 relationship isn't inherent in the

data, it has to be enforced by the programmer. <<

Exactly right: autonumberings is not in the data. It is created
outside the reality of the data model in the PHYSICAL storage;
unverifiable, random, irreproducible and proprietary. Would you take
a drug that uses that kind of "science"? No. So why do you run
databases like that?
you get situations where, for instance, a customer's order is

entered twice and gets two different autonumber "Order Numbers." <<

It is worse if there is a real key; one UPDATE uses the autonumber and
another uses the real key. If I were using two reald keys -- say,
Dewey Decimal and Library of Congress in a library -- I can
co-ordinate them. But with a phyasical locator, I am screwed.


It's quite possible for there to be two natural keys in an entity, but
neither the Dewey nor LoC codes are natural. They are both examples of
autonumbers that have become visible outside the systems that created
them.

In an entity where there are two or more natural keys it is unlikely
that either could change independently of the other, because either key
uniquely identifies an object. If a key changes then the object it
identifies changes.

I once suggested that the precise latitude, longitude, altitude and

time of birth would make a good natural key if we could persuade
people to record it.<<

That (location and birthdate) is the Swedish nation id number!


They are part of the data that is used to generate the British
equivalent of the US social security number (SSN), it's called the
National Insurance number or NINO. There have been a few incidents where
people with the same name have been born in the same place on the same
day, and have been given identical NINOs.

That illustrates a point about autonumbers. If you trust the autonumber
issued by some third party you can use them as if they were a natural
key. Some American organisations have used the SSN as a primary key in a
table and discovered that it's not quite good enough to be trusted as if
it really were a natural key.


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
Nov 13 '05 #16

P: n/a
"James" <dr*********@hotmail.com> wrote in message
news:63**************************@posting.google.c om...
Because there is absolutely no way that you can define a natural key
that will never change, nor one that is guaranteed to be unique
throughout all of time. Autonumbers are both as long as the
appropriate RI have been established. At least they are supposed to
be - there was a bug in Access 2000 I believe, that has since been
fixed, where autonumbers were duplicated, but that was a bug.
Is there really anything in life that is guaranteed to NOT change?


PKs are supposed to be stable. That isn't the same as saying they won't ever
change.
I supposed there are a few things, like the race of a person.
Whhhaaattttt? Go on, Mr Anthropologist, define human race for us. And lets
see what the mess we end up in there. Presumably that was your idea of a
joke.
You might
think social security #'s, but people even receive new of those from
time to time. ;-)
Well, if you live in a country with social security numbers. And even in the
USA they have been duplicated, apparently.
My IT manager at work even goes so far as to say that PrimaryKeys
should be totally meaningless in a table; in other words, if your PK
is made up of any part of your data, then you are not using a correct
PK. I had to kindly disagree with him ;)


Ha ha.

Primary keys are a problem. We're 'supposed' to take them from 'natural'
data. Something 'about' the instance of the entity type uniquely identifies
it. Something we could describe in words will make it possible to
distinguish it from another instance of the entity type. You're right, once
you start looking for an absolute guarranteed unique PK it gets a bit
difficult. Especially when we remember that in the relational model PKs
can't include nulls.

So whaddya do? The best you can within the constraints of the fact that the
relational model is an attempt to organise a world of things which isn't
relational.

Is it persons? You could put a unique (maybe even no nulls) index on first
name, last name, DOB (those 3 alone would be pretty strong, depending on
number of records), SSN, 1st Line of address etc. Then use an autonumber as
your 'PK', actually surrogate key.

It's always going to be a compromise.

Mike
Nov 13 '05 #17

P: n/a
-
No, there isn't. Thanks for making the point for me. ;-)

Then why do we worry about establishing a PK that will never change,
especially when the database provided the ability to cascade updates
to all related records?



I
supposed there are a few things, like the race of a person. You might
think social security #'s, but people even receive new of those from
time to time. ;-)

My IT manager at work even goes so far as to say that PrimaryKeys
should be totally meaningless in a table; in other words, if your PK
is made up of any part of your data, then you are not using a correct
PK. I had to kindly disagree with him ;)


And you did so on what grounds again?

The grounds that I used was the opposite opinion of his; that a PK
should be a representation of the existing data in a table. For
another, if a query a foreign reference table and the PK is one that
makes no sense, then I have to go and join 1 or more tables just to
determine what the data is related to.
Nov 13 '05 #18

P: n/a
> And if there are "child" records, neither an Autonumber or a "hand rolled"
ID function should resequence to "fill in" for missing values in the
sequence. Unless, of course, you have the additional procedures to modify
ALL the child records!


My wording was wrong; I didn't mean resequencing on a hand rolled
method, but if the tables have to be recreated, then the relationship
is easily re-established because there are values to related to in
each table, unlike an autonumber field which will contain all new
values.
Nov 13 '05 #19

P: n/a
In message <ko********************************@4ax.com>, John Nurick
<j.*************@dial.pipex.com> writes
Bernard, we're in Humpty-Dumpty country here. You're using the term
"autonumber" in a quite different sense from other people.

For the rest of us, an "autonumber" is a unique value _that the DBMS
itself assigns_ to a _record_ when the record is created, typically
though not necessarily sequential or random, but in any event unrelated
to other attributes of the entity represented by the record.
An autonumber is created in the database, but what is its counterpart in
the logical data structure which is independent of the database?.

Attributes such as SSN, ISBN, Dewey or LoC "numbers" are _not_
autonumbers in this usual sense.
But they are, the difference is that they are created in someone else's
database.
First, the mechanism by which they are
assigned is outside the DBMS.
It's outside the database which we control. It's in someone else's
database. That is irrelevant when considering the logical data
structure.
Second - and in consequence - they are
assigned to the _entity_ (the person, book, subject or whatever), not to
the record representing it. IOW for the purposes of the DBMS they are
natural attributes.
Natural keys are ones where the 1:1 mapping between the entity and the
database representation of the entity is guaranteed. If you trust the
agencies who generate keys like the SSN then you can treat them as if
they were natural keys, even though they are only autonumber fields in
those agency's own databases.

Third, Dewey, LoC and ISBN - I don't know about SSNs - are meaningful
"smart keys" which can be parsed to obtain values of various attributes
of the entity. Generating or assigning subject classifications such as
Dewey and LoC is ultimately a matter of librarians' judgment.


I agree, and this was sloppy thinking on my part. The Dewey decimal code
isn't actually a primary key in its usual sense. The Dewey code doesn't
uniquely identify a book, it only identifies a category into which books
can be classified. The LoC classification is similar.

The ISBN identifies a title and not a book. To be even more specific it
identifies one or more editions of a specific title. To identify an
individual book a library will use another code, typically an
autonumber, called the Accession Number.

The SSN is supposed to identify a unique individual, although there have
been mistakes in that mapping. In the UK the equivalent code is
generated from the time and location of the individual's birth, and if
more than one person is born in that place and time a serial number
(essentially an autonumber) is also used.


--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.

Nov 13 '05 #20

P: n/a
Why would you EVER care what id ms-access uses for the relation?

Do you care what memory segment word gets loaded into? Do you
care if it is memory segment 32, or 8192?

Who cares about a number you, and your users will NEVER see?

An autonumber is some mechanistic to generate a number. To you and me, all
we care about is that we have a relation from customers to customers invoice
table. Do we really care, or have to know what number is used?

Really, when word loads into memory, we don't care about the number used for
the memory location. Really, when ms-access has a relation between customers
and the invoice file...again we don't give a hoot about what number is used.
Me, or you never sees the segment number when word loads, and we as users
will never see the autonumber either.

These numbers are NOT for humans to see.....

There is a ZILLION kinds of internal numbers that your computer uses all day
to function. Why do you care what memory segment numbers the computer used
to load ms-access, or ms-word? Why care?

If your folks can see, or use those autonumbers...then that is your problem
with autonumbers.

The real wrong being done here is that users can see, or use the
autonumbers. I mean, do you want ms-word to start showing you the memory
segment numbers it uses to load a document into memory? It would be crazy to
force users to deal with memory segment numbers when using word.

With ms-access, YOU NOW are the software developer. So, just like those
developers who creased word, they don't show users what memory location the
documents load into. You as a developer has a responsibly to NOT LET USERS
see the autonumber.

If you need some number for your users, then you need to write your own
custom code that generates those numbers for human consumption (say, things
like invoice number etc.). You do NOT want to use the invoice number for
relations etc (you still use a internal autonumber, and that way you don't
even care if the invoice has a invoice number, or perhaps you wait a
specified time until a invoice number is given. Either way, you can still
have your relational database function...but behind the scenes it is using a
autonumber).

Your database should not crap out just because you don't have a order number
handy. Who even cares if you enter a order number, or not? Why should your
database stop function if you don't enter a order number? Even if you change
the order number, again..why should your database not work? Maybe you need
to delete the order number? (again, what on planet earth does deleing some
arbitrary number like the order number HAVE ANYTHING to do with building a
functional relation between two tables? How possibility does these two
separate concepts have anything in common?).

You users should NEVER EVER see a autonumber.

You mistake here is to try and let humans see, or even refer to, or use the
autonumber in any way. Autonumbers are NOT to be given meaning by
humans...but ONLY to your software.

Who cars if you have a order number, or not? What does the fact of having a
order number have to do with your database to functionally correctly? If you
want to require that a order number HAS to be entered, then make the order
number a required field, but that simple stupid order number HAS NOTING to
do with setting up a relation between two tables.

Setup your relation between tables with internal numbers, and your database
will JUST WORK REGARDLESS of what fields, and things you decide to store as
data. Do not go and attached some number out of the blue like a stupid order
number to build relations between your tables. Can you imagine if products
like QuickBooks, or even products like ms-word exposed internal numbers used
for relations and other internal numbers as to how the software will
function? Software uses ZILLIONS AND ZILLIONS of internal numbers and
pointers to function.

Now that YOU ARE the software developer, it is up to you to hide these
numbers. You can expose these internal numbers (like autonumbers), but that
is just rude, and just services to torture your users. Hide all the internal
number stuff....every other developer before you did this....
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.attcanada.net/~kallal.msn
Nov 13 '05 #21

P: n/a
James,
Before the turn of the milennia I managed to kick off a long winded and
passionate thread complete with troll posts and flames on the issue of
whether arbitrary or significant keys are better. This tends to be a bit of
a sore subject for some. I maintained then that in all cases arbitrary keys
are better. Since then I've had consulting contracts where I inherited a
schema that used significant keys and imposing my own arbitrary keys would
have unleashed a great deal of havoc for the client. I did the smart thing
and left things as they were. These days I tend to include both an
arbitrary key maintained by the rdbms that is the primary key and does
uniquely identify that row in a table as well as any significant keys the
client needs to maintain compatibility with their existing systems.
Processor, disk space and memory is cheap these days so any overhead
generated by the additional columns is probably cheaper than an overhaul of
the schema to shift to or from an arbitrary or significant key naming
convention.

"James" <dr*********@hotmail.com> wrote in message
news:63*************************@posting.google.co m...
I have been reading many posts by users that claim to have years of
experience with Access that promote the use of Autonumber generated
values as primary keys. Don't get me wrong; I use Autonumber values as
primary keys for standalone tables, such as those that really do not
have a primary key unless you use multiple key fields. But I keep
asking myself why anyone would create an architecture of two related
tables upon which the relationship is based on an Autonumber key
field. I'm currently working on one right now where I'm converting an
"Orders" table that uses Autonumber to generate Order numbers. This
Autogenerated value is then stored in the OrderDetails records. If the
scenario that you describe below occurs, guess what? You've just lost
your relationship between the two fields, because you cannot enter
your own values in an Autonumber field, and your OrderDetails table is
useless because the reference to the Orders table is gone. The
OrderDetails table will use an Autonumber key to basically serve as a
surrogate primary key, but I use a "natural" key that I control -
which in this case is my own "rolled" invoice #. The database I'm
converting is going to use my own "rolled" method, upon which a
function will return an incremented value from a table. With the
experience I've had with tables related based on Autonumbers, I will
never willingly design one to be as such.
"Jeff Boyce" <Je**********@msn.com-DISCARD_HYPHEN_TO_END> wrote in message

news:<O8**************@TK2MSFTNGP09.phx.gbl>...
Traci

In addition to the cautions noted by Doug in his response, be aware that if your table's primary key (?your Autonumber) is used as a foreign key in
one/more child tables, "restarting" your Autonumbers could hose the
relationship between parent and child records.

Nov 13 '05 #22

P: n/a
> PKs are supposed to be stable. That isn't the same as saying they won't ever
change.
I supposed there are a few things, like the race of a person.


Whhhaaattttt? Go on, Mr Anthropologist, define human race for us. And lets
see what the mess we end up in there. Presumably that was your idea of a
joke.

HAHA! Ok - I was trying to give a basic example, but apparently this
wasn't a good choice. Point taken! :-)
Nov 13 '05 #23

P: n/a
Albert,

Your passion to the subject at hand is duly noted :-)

However, I have had to work on several MS Access databases where the
original developer decided to use autonumbers to relate all the tables
to each other. The ones that I address were corrupted and the only way
to recover the data was to basically recreate the database and import
the data. When your autonumber values are all regenerated at this
point, it is a nightmare to recover the relationship - at least it is
for me. If you have an easier way, then I'd sure like to know what it
is.

I recently built an application (ASP.NET and SQL) that created an
entry ID that the user did care about - and when the user saved the
record, an ID was generated (not autonumber either :-)) but the user
was not able to see it until an approved process was followed. In your
example of where an invoice number might not be created until later,
but an entry was still allowed, I think a better solution would be to
store this in a temp table, and then move it to an actual "Invoice"
table after an invoice number is assigned. If my table already has a
unique identifier that has meaning, I just see no need to add another
field for an identifier that has absolutely no relation to my data.

Here's another example I'm considering:

I'm mulling over constructing a membership database for my church.
Since a natural key would be hard to come with (most people would not
be willing to give up SSN, if I even wanted to use that), my thoughts
are to assign a membership ID from a custom rolled function that
basically increments a integer value in a single row, single column
table. This basically will be my "autonumber", only that I am
controlling it, and it will have an "artifical" relational meaning
because I can look up users - as well as anyone else - via their
membership ID. It will then be upon this membership ID that relations
to other tables will be established. If the database crashes - then
fine, I can deal with that, but I don't want to worry about my
database reassigning membership ID's if I were to base it on an
internally controlled, incremented value when the database is
recreated, or the data has to be repaired by importing into another
table of the same structure.
Thoughts?
James
Nov 13 '05 #24

P: n/a
In message <3iDKc.53536$Mr4.10789@pd7tw1no>, Albert D. Kallal
<Pl*******************@msn.com> writes


The real wrong being done here is that users can see, or use the
autonumbers. I mean, do you want ms-word to start showing you the memory
segment numbers it uses to load a document into memory? It would be crazy to
force users to deal with memory segment numbers when using word.
That isn't really a problem at all. The fact that the US government
allows people to know their own SSN doesn't really present a problem at
all.

With ms-access, YOU NOW are the software developer. So, just like those
developers who creased word, they don't show users what memory location the
documents load into. You as a developer has a responsibly to NOT LET USERS
see the autonumber.
No, that's not true. The limitation is that once you have published the
autonumber you can't change it.

If you need some number for your users, then you need to write your own
custom code that generates those numbers for human consumption (say, things
like invoice number etc.). You do NOT want to use the invoice number for
relations etc (you still use a internal autonumber, and that way you don't
even care if the invoice has a invoice number, or perhaps you wait a
specified time until a invoice number is given. Either way, you can still
have your relational database function...but behind the scenes it is using a
autonumber).
So now you want to create two autonumbers and hide one of them. Don't
forget that the invoice number is itself an autonumber.

Your database should not crap out just because you don't have a order number
handy. Who even cares if you enter a order number, or not? Why should your
database stop function if you don't enter a order number?
Because your users need a key to retrieve information from the database.
If you don't have a good natural key then you have to use an autonumber
instead.
Even if you change
the order number, again..why should your database not work?


Your database will work. Your data processing system will not, because
it includes the users who want to get data out of the database. Changing
the number inside the database does not change the numbers in their
head, or on existing documents.

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.

Nov 13 '05 #25

P: n/a
"Bernard Peek" <ba*@shrdlu.com> wrote in message
news:ay**************@shrdlu.com...
So now you want to create two autonumbers and hide one of them. Don't
forget that the invoice number is itself an autonumber.


You use autonumbers for invoice numbers? What happens if you cancel the
insert of a record? Won't the auditors be interested in the missing invoice
number?
Nov 13 '05 #26

P: n/a
"Bernard Peek" <ba*@shrdlu.com> wrote in message
news:wI**************@shrdlu.com...
In message <63*************************@posting.google.com> , James What you have to bear in mind is that there's a difference between a
table and an entity. Entities are in the logical data structure and
tables are in the physical data structure.


Nope. Entities are a way of describing things in the conceptual model,
relations are used in the logical model. The conceptual model and the
logical model are not the same thing.
Nov 13 '05 #27

P: n/a
James

Actually, it isn't all that uncommon to need to rebuild a "autonumbered"
table. One of the ways to do this is to create a new table definition, with
an autonumber field, then append from a source table/query, and append in a
new autonumber.

Regards

Jeff Boyce
<Access MVP>

Nov 13 '05 #28

P: n/a
Yes, I agree - but my point is that everything will be completely
renumbered, and relationships built upon the autonumber will very
likely no longer be valid.

In this "original" state:

The values might be:

1,My #1
2,My #2
3,My #3
4,My #4

If # 2 is deleted, for example, then rebuilding the autonumber will
result in:

1, My #1
2, My #3
3, My #4

etc.....

"Jeff Boyce" <Je**********@msn.com-DISCARD_HYPHEN_TO_END> wrote in message news:<#Y**************@TK2MSFTNGP10.phx.gbl>...
James

Actually, it isn't all that uncommon to need to rebuild a "autonumbered"
table. One of the ways to do this is to create a new table definition, with
an autonumber field, then append from a source table/query, and append in a
new autonumber.

Regards

Jeff Boyce
<Access MVP>

Nov 13 '05 #29

P: n/a
Alan -

I can see your point. I didn't just decide in my case to stop the
presses and undertake this mission; I was in the process of converting
the backend portion to SQL Server and when I saw the mess that the
developer (in this case, a manager of the company who had just
completed a level 1 MS Access class.......) had made. That is why I
chose to do this in this scenario.
Nov 13 '05 #30

P: n/a
In message <40***********************@news.aaisp.net.uk>, Mike MacSween
<mi******************@btinternet.com> writes
"Bernard Peek" <ba*@shrdlu.com> wrote in message
news:ay**************@shrdlu.com...
So now you want to create two autonumbers and hide one of them. Don't
forget that the invoice number is itself an autonumber.


You use autonumbers for invoice numbers? What happens if you cancel the
insert of a record? Won't the auditors be interested in the missing invoice
number?


I wouldn't use the autonumber function in Access for that job, but
something similar is the usual way of creating invoice numbers.
Typically it's wrapped up in a transaction.

I've already pointed out that one of the disadvantages of autonumbers is
that people try to read some subtle meaning into them, which is why it's
often desirable to deliberately obfuscate the way the sequence runs. For
some purposes I would prefer a sequence that is predictable but not
obvious.

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.

Nov 13 '05 #31

P: n/a
"Bernard Peek" <ba*@shrdlu.com> wrote in message
news:eo**************@shrdlu.com...
In message <40***********************@news.aaisp.net.uk>, Mike MacSween
<mi******************@btinternet.com> writes
"Bernard Peek" <ba*@shrdlu.com> wrote in message
news:ay**************@shrdlu.com...
So now you want to create two autonumbers and hide one of them. Don't
forget that the invoice number is itself an autonumber.
You use autonumbers for invoice numbers? What happens if you cancel the
insert of a record? Won't the auditors be interested in the missing invoicenumber?


I wouldn't use the autonumber function in Access for that job, but
something similar is the usual way of creating invoice numbers.
Typically it's wrapped up in a transaction.


You've confused the issue by using the term autonumber out of context. The
OP clearly was talking about the Access things called autonumbers.
I've already pointed out that one of the disadvantages of autonumbers is
that people try to read some subtle meaning into them,
Well, if you're talking about your system generated numbers being used as
invoice numbers, then I'm sorry, invoice numbers do have meaning.
which is why it's
often desirable to deliberately obfuscate the way the sequence runs. For
some purposes I would prefer a sequence that is predictable but not
obvious.


Why?
Nov 13 '05 #32

P: n/a
In message <40***********************@news.aaisp.net.uk>, Mike MacSween
<mi******************@btinternet.com> writes
which is why it's
often desirable to deliberately obfuscate the way the sequence runs. For
some purposes I would prefer a sequence that is predictable but not
obvious.


Why?


The last time I needed to do this we were issuing a new identity code
for about 150,000 items owned by around 20 different companies. I didn't
want there to be any obvious correlation between the numbers issued and
the company they were issued to. So I issued numbers in blocks of
between 1,000 and 5,000 and rotating between companies. I also made sure
that there were numbers issued internally so that there would be gaps in
the range.

If there appears to be a pattern in the numbers then some people will
build business processes on the basis of this perceived pattern. Then
when you come to change the system you may find that you are constrained
by other people's misinterpretations. Worse still the predictability of
the numbers may be a security risk, I've already quoted an example where
a predictable number sequence cost millions in fraud.

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.

Nov 13 '05 #33

P: n/a
James

I believe you and I said the same thing.

It doesn't matter what the underlying mechanism is for generating the IDs.
What does matter is not "orphaning" child tables by resequencing a main
table's key.

Jeff

Nov 13 '05 #34

P: n/a
"James" <dr*********@hotmail.com> wrote in message
news:63**************************@posting.google.c om...
Albert,

Your passion to the subject at hand is duly noted :-)
Yes..I was a bit too excited here!

However, I have had to work on several MS Access databases where the
original developer decided to use autonumbers to relate all the tables
to each other. The ones that I address were corrupted and the only way
to recover the data was to basically recreate the database and import
the data. When your autonumber values are all regenerated at this
point, it is a nightmare to recover the relationship
The autonumbers should not be have been re-generated at this point.

The autonumbers do in fact remain the same if you export to another file. If
the database was so badly damaged that you could not read the autonubmers,
the likely hood of this is no greater, or less then your invoice number
field being damaged also.

If something happens to the actual numbers, then in both cases, the system
will fail. So, I don't see why you had to re-generate the numbers. In fact,
using update queries keeps the autonumbers the same. I am at a loss as to
why you had to re-generate the autonumbers here?
but an entry was still allowed, I think a better solution would be to
store this in a temp table, and then move it to an actual "Invoice"
table after an invoice number is assigned.
Wow? start using some temp tables? Now, you developer team has to maintain
two sets of tables (and, if your database has ANY KIND of decent
normalizing, your simple invoice is likely to be 4 or 5 tables deep here.
Sorry, starting to use temp tables to solve a problem of NOT yet having a
invoice number is a rather huge expense and increase in developer time. Not
only do you now have to maintain two sets of tables, but now start writing
reams of code to append the data from one set of tables to another. Further,
if you add new fields, or even more tables to the design, then that append
code to move the data out will have to modified each time. Your idea is a
VERY VERY expensive idea in terms of labor and design cost. All of this
change to the system can simply be eliminated by hiding autonumbers from
your users!
If my table already has a
unique identifier that has meaning, I just see no need to add another
field for an identifier that has absolutely no relation to my data.
Yes, but business rules change all the time. This year, the boss might say
we are not going to use invoice numbers anymore, and just use some kind of
PO number. With your design, you could result in YEARS of development time
to make this simple change. In my example, we can change and forget about
invoice numbers and start using PO numbers tomorrow. This is just question
of freedom of design. There is no question that this number has no meaning,
and this is exactly WHY I am suggesting to use it.

Here's another example I'm considering:

It will then be upon this membership ID that relations
to other tables will be established. If the database crashes - then
fine, I can deal with that, but I don't want to worry about my
database reassigning membership ID's if I were to base it on an
internally controlled, incremented value when the database is
recreated, or the data has to be repaired by importing into another
table of the same structure


As mentioned, I never had, or seen the problem of exporting data with the
autonumber. I seen NO evidence that restoring data from a crashed database
is any more difficult when auotnumbers are used as compared to some exposed
number. If the numbers and data can be read..then you can retrieve the data
in both cases.

Further, once again, but NOT using the membership ID number, you gain a LOT
more freedom. Some new people may not yet be members, but only visitors, but
you still want to track donations and attendance. Further, you obviously
will have some table structure that has Family->children.

Again, some families may attend, may donate money etc, but not yet have a
membership id. What do you do now? They are just visiting families.

Then you see all kinds of crazy stuff like well...ok memberships id's in the
8000 range are for visitors..and non members. Fact is, why even have to know
the future and worry about some problem(s) based on the fact that you MUST
have a member ID to function? And, if you have both visitors and
members..then you will have gaps in the membership id's. Fact is, why not
design your system to function with, or without membership id?

Further, some people in the church may have all kinds of things that change.
(divorce, have children, or children now become their own families etc etc).
Attaching a ID number to the relationship is going to reduce your ability to
move records around. If you need membership ID, then create a membership id.
However, once again, I don't see how a membership ID has anything to do with
your relational database functioning correctly just because you do, or do
not have a membership ID yet.

You don't care, or save the disk sector numbers on the disk drive. This is
just all interanal nuts and bolts stuff that the computer worries about...
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.attcanada.net/~kallal.msn
Nov 13 '05 #35

P: n/a
> >
With ms-access, YOU NOW are the software developer. So, just like those
developers who creased word, they don't show users what memory location thedocuments load into. You as a developer has a responsibly to NOT LET USERSsee the autonumber.
No, that's not true. The limitation is that once you have published the
autonumber you can't change it.


Ah, but why can't I change the autonumber? That is exactly my point. Why
publish the autonumber? This is my WHOLE POINT!

Why and where did this rule come from? In fact, since users DO NOT care or
know about this number, I am in face FREE TO CHANGE the autonumber when I
want, and as often as I want, and how I want! This is complete freedom for
the developers of application, and they don't have to care, or worry, or
even be hamstrung by their users complaining that some internal number used
for relations changed on them.

The goal here is to give those software developers freedom to develop
software that simple works, and the user of the software should not care, or
haw to worry about this stuff.

In fact, my developers should be able to change that number as often as they
like. And, if we are smart and don't expose this number, then we DO HAVE
this complete freedom. Further, with modern database engines, cascade
updates of the child keys is a common thing, and even old systems like the
JET engine for ms-access has this feature.

So now you want to create two autonumbers and hide one of them. Don't
forget that the invoice number is itself an autonumber.
Ah, perhaps some confusing here. We were/are talking about autonubmers (but,
really, the same applies to @indent fields in sql server).

So, no..., the invoice number SHOULD NOT be a autonumber. The real problem
here is that you have NO control of how autonubmers increment.

You also don't have control of what memory segment number ms-word loads
into. No one is suggesting to start tagging our word documents with memory
segment numbers, or perhaps the track and sector numbers on the disk drive.
These are just internal numbers stuff that the computer needs.

Really, the autonumber is the same thing, and there is NO control of how it
generates and creates numbers. It can even be set to random if you wish! A
invoice number for all practical purposes CAN NOT be a autonumber, as they
will and can change on you. So, for something like a invoice number, one
likely has to roll their own system to generate numbers..

Because your users need a key to retrieve information from the database.
If you don't have a good natural key then you have to use an autonumber
instead.
Sure, that makes sense.

However, for most searching of people, some customer id, or perhaps just
searching by name is MORE then sufficient. Maybe as the customer moves from
being a simple mailing contact to a full blown customer, then the a good
natural key will arise out of the data. However, in this modern fast paced
world, we OFTEN DO NOT on initial customer contact have a very good natural
key. Further, why should my dataprocessign system care about internal memory
segment numbers, or internal numbers used for relational between tables? In
fact, going all the way back to punched cards, as those cards are processed,
little care or need of some customer ID is needed for HUMANS to use.

Maybe all the results of the search will show pictures of the people, and by
how the person looks, I will pick that person!

Of course, at some point (perhaps while talking on the phone) a good natural
key may become available. I am all for using natural keys to search and find
the customer. But, what the heck does searching for customer have to do with
my application working, and what kind of disk drive I going to choose here?
Why should my customer id, or the kind of disk drive I use have anything to
do with me allowing relations between tables? Why such a HUGE restriction
here? You mean, I have to have a good natural key AND THEN my application
works? Or, if I choose IBM hard disk, the system will work, but with a
Fujitsu drive..it will not? These issues are that of the machine. I want
freedom here! Maybe I do have a good natural key, maybe I don't!

My software should work just fine with, or without a good natural key. I am
at a loss as to why identifying a customer has anything to do with ms-word
loading in memory segment FE1EE20000? (or some internal pointer number to
identify a relation between two tables?)

And, further, who cares is a natural key is, or is not available? Hum, I
often wanted to print a barcode on the forehead of each customer! ;-)

Good natural keys are great, and if you come up with a great natural key for
your system, then it certainly is useful. However, identifying customers by
some natural key should not effect the ability of my software to function.

Your database will work. Your data processing system will not, because
it includes the users who want to get data out of the database. Changing
the number inside the database does not change the numbers in their
head, or on existing documents.


Exactly, and I should be free to change, or do whatever I want with those
internal numbers. Can you imagine if we restricting what part of memory
ms-word loads into based on some internal pointer number that some person
decided to use for customer id?

Why should your car have restrictions based on the name, and color of the
clothes of the person driving the car? The computer is a machine like a car,
and both should NOT expose their internal operating systems and numbers used
to function. Why use part of the machine design to identify customers?

The function of the computer should have little, or nothing to do with the
issues of users having some means to identify a customer. We certainly need
a way to identify a customer, but that has NOTHING to do with my accounting
system, or CRM system ability to function. (running the software that posts
the payroll at the end of the month does not care about a external, or
internal id used. It needs a number, but so does the fuel injection system
in your car).

And, my car should not care less about the license plate, or the color of my
skin, or the color of the paint, or my driver license number when I drive
it.

That car should just work!, and so should my software too!

There are certainly arguments and some advantages to using a natural key,
but most of the time that issue is one of identify a customer, not some
memory value or pointer used to connect (relate) two tables.

Why restrict the function and flexibility of the machine (or software) on
extra external stuff when we DO NOT have to?

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.attcanada.net/~kallal.msn
Nov 13 '05 #36

This discussion thread is closed

Replies have been disabled for this discussion.