Re: Primary Key and autonumber

>> Let's assume I have a database that has 1 million records, all
uniquely identified with a Primary 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? <<

Even if you have some kind of auto-increment function or datatype in your product, do not use it. They are all proprietary, non-stadnard and non-relational. They are also all subject to assorted screw ups.

You have been given the major methods:

1. INSERT INTO Foobar (key_col, ...) VALUES ((SELECT MAX(key_col) FROM Foobar)+1, ...);
2. Write a stored procedure that accesses a table of last used keys and increment the value.

However, both of these methods have a problem. Instead of an simple increment, you want a number with a check digit if a human being ever has to use it for data entry and you really want a random number so that the indexes are not re-balancing their tree structure every time you turn around. You can get good random number generators with cycles of two to four million numbers from Dr. Dobbs Journal or a textbook.

--CELKO-- Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Jan 08 2000 - 00:00:00 CET