Re: Primary Key and autonumber

In article <38735E63.581D1E1C_at_erols.com>, Francois Merle <leurmy_at_erols.com> writes
>Let's assume I have a database that has 1 million records, all uniquely
>identified with a Primary Key.

This is a unique key.

>If my database does not provide an Autonumber for the Primary Key, how
>can I efficiently give each new record a unique key without going
>through the entire database to check if that value has already been
>taken?

Your database already has a unique key, you have already told us that.

What you can do is make sure that the index on that key doesn't accept duplicates. If you do that then any record you succeed in adding will have a unique key.

If you want to add an additional unique key then:

1. Add a new numeric field to the table. Perhaps call it ID.
2. Write a program that writes the record number into that field
3. Create a new table with one numeric field and one record
4. Write a number 1 greater than the greatest ID value
5. When you need to add a new record
   1. Retrieve the number from the new table
   2. Add 1
   3. Save this new number to overwrite the old one
   4. Use the new number as the ID in your main table

It's important to do this in the right order. The most likely failure mode will leave gaps in the numeric sequence. If you use this to generate invoice numbers the auditors might get suspicious about the gaps.

-- 
Bernard Peek
bap_at_shrdlu.com
bap_at_shrdlu.co.uk