What are the tried and true methods for generating keys?
See also: Primary Key Data Types
UUID or GUID:
- Several years ago Digital came up with a strategy called UUIDs for determining generating unique key values based on hashing the value of the identification number of the Ethernet card within your computer and the current date and time, producing a 128-bit string that was guaranteed unique. For computers without Ethernet cards you could obtain a identification number stored online in a file. Microsoft has a similar strategy called GUIDs that also results in a 128-bit string. Both of these strategies work well, although both strategies are proprietary and do not run on all platforms.
- For the windows environment there is an API call that will generate a GUID. If someone wants to donate some GUIDGeneration Code here that would be great.
- A UUID (or GUID) is not guaranteed to be unique, because it depends on the machine (for instance, the network adapter) and the time. Microsoft states: "To a very high degree of certainty, this function returns a unique value". This means with GUIDs as primary keys, you have to be prepared for uniqueness violation errors and create a new key in this case.
- Yes, they are slower than incremented integers, but when you use them, suddenly those "uniqueness of primary key" errors vanish
- SQL 7.0 adds a datatype called uniqueidentifier which is designed to specifically hold a GUID, it it not however automatically populated. You could do this with a default for the column if you wished. T-SQL also adds the NEWID() function which will generate a GUID.
- For each primary key field maintain a counter which keeps track of the next key to be allocated.
- Generally implement with a default value which calls a stored procedure passing the Key Field name. The stored procedure does a look up in a 'id' table which maintains the lastid. The ID is incremented (see Primary Key Data Types) and stored back into the ID table, then the new value is returned and used as the new PK.
- Be sure to make sure that the same key is not assigned to two processes (like different users).
- Effective Techniques presents a solution that does not require locking, and will work for local and remote data. Also see Mk Key.
- Also consider Header Locking as an alternative to File Locking.
Auto Incrementing Field
- This is esentially a counter implemented by the database software.
- Most databases allow auto-incrementing integer data types.
- SQL Server calls these fields IdentityField. There are Identity Issues.
High Low Key Generation
- The basic idea is that instead of using a large integer for the OID, requiring you to go to a single source (and therefore a bottleneck) to obtain the OID, you reorganize your OID into two logical components: A
HIGH value that you obtain from a single source and a LOW value that the application assigns itself.
- Please see High Low Key Generation for more specific information.
If you have disconnected locations of data entry, like laptops, keys need to be assigned and either reassigned when the data is added to the "master" database, or some method of making sure that as keys are generated, they are still unique. the guid is one solution. Pre allocating keys in batches is another.
Location of the procedures to generate keys
For VFP stored procedures in the dbc is a natural place to put this routine. There are disadvantages to stored procedures: odbc drivers don't fully support the VFP command set (or so I'v heard), harder to update without taking down the system.
MiddleTier components, accessible via COM, are another candidate location.
Running out of Key Values
What policies do real developers adopt when they run out of unused identifiers?
I was faced with the reality that in 20 hours, my client would enter timecard 1,000,000. You guessed it, 6 digit key field. After some wild ideas, we set the counter to -99,999 and bought ourselves a few months. We later added a few chars to the field. -- ?CFK
In one instance I was using character values and switched from base 10 to base 16--employing A-F above 0-9. -- Randy P
Our order system was just like Randy P's except I went to base 36 using 0-9 then A-Z. Sadly we will be replacing this system some time this year, I'd hoped to see it run long enough to produce reams of orders with 4 letter words in the keys.
See Also Base X Mk Key Primary Key Data Types
Contributors: Carl Karsten, Randy Pearson, John Koziol, Christof Wollenhaupt, Bob Archer, Dafydd Rees
Category Data Category Principles And Guidelines Category Key Fields Category Object IDs
( Topic last updated: 2006.01.26 02:45:08 PM )