The only way to avoid this problem is to add insert and delete triggers. As
you insert, the trigger pops the next available number off of the heap. The
heap is a table that holds the next increment as well as the numbers for any
deleted records. As you delete records the trigger pushes them onto the
heap. Use two columns, one to hold the number, one to describe the number.
This way you can tell if the number came from a delete ( in which case all
you do is delete the record. ) If the number is a new number, you must
increment it and update the number. The trick is concurency and how you will
handle multiple requests. Remember to place a unique index on the column
both in the target table and in the heap.

Trust me, it a lot of work for questionable results. You would have to have
a very very compelling resaon to need to do this.



Thank You
Donald C. Burr
dburr@s... <mailto:dburr@s...>



-----Original Message-----
From: Nicolas Raitman [mailto:new_world@r...]
Sent: Thursday, September 28, 2000 5:59 PM
To: professional vb
Subject: [pro_vb] Database Design II


Hi to all. I have another question on Database Design. Supose that I
have a table and it's primary key as autoincrement. It all works ok,
I add fields until I have 10. No matter why, but I decide to delete the
field number 3, and the next time that I will add the record, the fields
will remain the same, I mean even though I deleted one, is not that all
the fields will decrease by one. I guess this is right, but is there a
way to achieve this?
Also, I have another problem. The previous one was not that critical for
my needs, however, supose that I delete the entire table (I mean all the
records in the table), the field of the autoincrement, the next time that
I add another record will have the value of 11. I want that value to be
0, is there a way to do this?

Please I really need help in Database Design (the previous topic I
posted) because I cannot go on with my project if I can not create the
table I explained there.

Thanks a lot.