Better alternative to autonumber primary keys

Question

I am looking for a better primary key than the autonumber data type, namely for the reason that it's limited to a long integer, when I really just need the field to reflect a number or text string that will never ever repeat, no matter HOW many records are added or deleted from the table. The problem is I am not sure how to implement something like turning the current date and time into a hexadecimal string and using that as a unique field I can use as a primary key.

Am I just being too paranoid about running out of space?

-- EDITED 03-16-2010 @ 1237 hours --

I had a person who, at the time, I thought was a wonderful reference for Access related questions tell me that Replication IDs are just a counter for the number of times an item was replicated... hence I never explored it further. After the number of replies, I have up-modded, and accepted an answer. I guess I was just having a stupid newbie Accesss developer moment. Seriously though, thank you again for everyone who replied!

Answer 1

Why do you think you'll run out of space? Perhaps you do not realize how big a 64-bit integer is, exactly. It allows for around 10 billion billion records. If you created 100 records per second, it would take over five billion years to run out of integers.

Answer 2

GUID. They are pretty unique

http://en.wikipedia.org/wiki/Globally_Unique_Identifier

You did not mention your programming language. C# would be something like

String myKey = Guid.NewGuid().toString();

Answer 3

Per John's answer, you're unlikely to run out of long integers. But if you'd prefer a unique string, the easiest solution is a UUID. It doesn't take inputs, but the odds of ever generating two identical UUIDs are negligible.

For example, in Python:

import uuid
uuid.uuid4()

There are UUID functions available in most all languages: http://en.wikipedia.org/wiki/Uuid

Answer 4

Why are you limited to a long integer? When you specify an AutoNumber field, you can tell it to use a Replication ID instead of Long Integer and it will be a unique 128-bit value called a GUID.

Although you can use the current date and time as a primary key, here's why not to:

The current date and time are not as unique as you might think. If you make records very rapidly, you could end up with two being inserted between clock ticks, causing both to end up with the same time. Or your computer's clock could just get reset backwards. Or DST could end and if you're storing local times, you'll end up with duplicate times.