Autonum fields for generating primary keys are limited to 2^32 (10^10)
keys. It is my understanding that they are not reused. What happens
when the last key is generated and then an attempt it made to add
another record?

--

If the field is limited to 2^32, it may fail to accept a new record
after (2^31)-1.

On Fri, 27 Jul 2007 08:06:04 -0700, Neo <neo55592 (AT) hotmail (DOT) com> wrote:

My previous post was not as clear as it could have been. What I meant
to ask was about reusing keys that had been previously assigned, but
whose records have been deleted.

If a table has a very large number of records added and then deleted,
eventually, the autonum field will count up to the limit for that data
type. Will the next record add request get an error even though the
table is almost empty?

--

Yes, at least in MS Access it will give an error. One way to get past
this problem, is to add an new autonumber id field, update links and
drop old id field. Suppose your data looks like this initially:

T_Person
ID Name
10 john
9999 mary

T_Address
PerID Addr
10 100 main st
10 9459 berkshire
9999 456 penny ave

Disable referential integrity between affected table. In T_Person,
convert original ID's datatype from autonumber to number. Exit table
design mode. Reenter design mode. Add field ID_NEW with datatype
autonumber. The numbers in ID_NEW will be sequential. Update IDs in
related tables via query. At this point, you data will look like this:

T_Person
ID Name ID_NEW
10 john 1
9999 mary 2

T_Address
PerID Addr
1 100 main st
1 9459 berkshire
2 456 penny ave

Delete original ID field in T_Person. Now you can rename ID_NEW back
to ID. Re-enable primary/referential integrity.

T_Person
ID Name
1 john
2 mary

Make sure to backup you db prior to attempting above, as it might take
several tries to get it right

That depends on your product and what release you have. Some do a
roll-over to negative numbers, some give you an overflow error and
some can re-set.

But a better question is what can the internal state of the hardware
mean in a correct logical data model? The answer, of course, is
nothing at all. It is a totally non-relational, highly proprietary
construct from assorted vendors.

You don't even know the differences between columns and field, rows
and records, so it is pretty sage bet that what you are doing is
mimicking a 1950's sequential magnetic tape file system in you SQL
product. You might want to consider a proper primary key in a logical
data model instead of a physical counter of events in the hardware.

--CELKO-- <jcelko212 (AT) earthlink (DOT) net> wrote:

preference. What key do you suggest people use when there is no
"proper primary key"? For example, there was once a proper primary key
of Order Number that was printed on the order form. The order is now
entered directly into the supplier's computer, possibly by the
customer's computers. Can you provide a proper primary key that is
superior to a computer generated one?

On Sun, 29 Jul 2007 08:24:58 -0700, --CELKO--
<jcelko212 (AT) earthlink (DOT) net> wrote:

Your "help", if that is even what you intended, would be easier to
take if it were seasoned with slightly less sarcasm and condescencion.

--

On Jul 29, 12:59 pm, David Segall <da... (AT) address (DOT) invalid> wrote:
Order Number is a valid PK. Think of the paper based system. Each page
or the Order pad is numbered and auditors validate using the
sequential nature of that number. Similarly your checking account uses
sequential check numbers.

However, most things in this universe do not come with ID numbers.
Humans long ago invented names to identify things. So in a table of
family menbers you might have used name for the primary key.

If you think about most entities, you can come up with a way to form a
key that is not a numeric ID. There are some cases where ID's work
better (Work Orders or Checks for example), but even those you could
come up with a valid candidate key other than the number. (go on, try
it!)

Are you arguing that the Person and Address tables are good cases of
using an ID column? I would argue that they are not such cases.


With respect to the original question, there are two main solutions:

Renumber IDs as described earlier, with all the attending danger
involved (consider a bug in the renumber program). If that is the
desire, it seems it should have been designed into the system from the
beginning.

Recognize the limit of this design and Convert the database. A simple
minded conversion would go to a DBMS with a 64bit numeric ID. A more
sophisticated conversion would research and uncover non-ID keys for
the tables for the new implementation, avoiding the recycled ID
problem.

Ed Prochak <edprochak (AT) gmail (DOT) com> wrote:

database generated primary key. As I said, CELKO would accept the
printed sequential order number as a primary key but seems to reject
the equivalent computer generated one. You are happy with a printed
cheque number but you don't seem to have accepted the database
generated Acknowledgement Number that I get when I pay by bank
transfer.
for a particular solution. A possible alternative "valid candidate
key" is not an argument in favour of using one.
club. The records are unique on First Name, Last Name, Street Address,
Suburb and the optional Date of Birth. What would you use as the
primary key?

I am not advocating a database generated key if a better one exists
but CELKO beat the OP about the head for merely asking what happens
when a generated key overflows. I am arguing that the question is
exactly equivalent to asking what happens when the numbering machine
that prints the cheque numbers overflows.

"David Segall" <david (AT) address (DOT) invalid> wrote



Agreed.