Fill in Autonumber gaps due to deleted records

[Guest]

How can i get autonumber to fill in the gaps that are due to records being
deleted???
urgent help needed!!

thanks!!!!

 

[Mike Labosh]

How can i get autonumber to fill in the gaps that are due to records being

deleted???
urgent help needed!!

This is not something that you should care about. The purpose of AutoNumber
is to supply automatic values for surrogate keys. You don't care what the
key is, just that you have something to join across.

the number one first rule of databases is that in the table storage, the
order of the rows and the order of the columns is irrelevant. Then you use
a query to sort stuff when you need to.

Just imagine the overhead incurred if database folks took the "sequential
contiguous autonumber" idea seriously:

Any time a record is deleted, the whole table would have to be renumbered,
and if it's a primary key, then the new values would have to be updated in
all related tables, oops, but wait, you can't modify an auto number, so
you'd have to programmatically change the data type to Number:Integer, then
run the updates and cascade the changes and then programmatically change the
type back to autonumber and then have to figure out what to do with the new
Autonumber trying to start at 1 when there is already a 1 record there....
YECH! NO WAY!

If *you* want to have sequential numbers, you should do that in a new
column. If some doofus is telling you to do that, kindly explain to this
person that this sort of thinking, while on the surface, seems to make
logical sense, is EVIL to a database.
--
Peace & happy computing,

Mike Labosh, MCSD

Feed the children!
Save the whales!
Free the mallocs!

 

[Douglas J. Steele]

You don't.

Autonumbers exist for one purpose: to provide an (almost guaranteed) unique
value to use as a primary key. 1, 4, 6 serves that purpose just as well as
1, 2, 3.

Autonumbers are NOT guaranteed not to have gaps. In fact, if you replicate
your database, the autonumbers will change to Random, rather than
Sequential.

Seldom, if ever, should the value of the autonumber field be shown to the
end users. If you're trying to assign meaning to the value of the autonumber
field, you should perhaps consider not using an autonumber.

 

[Guest]

hi,
you can't. that is the problem with auto number.
solution.....don't delete records.

 

[Joseph Meehan]

How can i get autonumber to fill in the gaps that are due to records being
deleted???
urgent help needed!!

thanks!!!!

Easy, don't show anyone the auto number and they will never know. You
and they will never miss them. If it is important to show them, don't use
auto numbers.

 

[Mike Painter]

How can i get autonumber to fill in the gaps that are due to records
being deleted???
urgent help needed!!

You can't and if the missing numbers are important then you should mark a
record void and not allow it to be deleted.