Automatic key generation is a complex topic, but in short, a database can choose new keys if it has a special table reserved for key generation. This table must have a list of unused keys in one column (the incrementor column) and a list of numbers in order starting with 1 in the other column (the identity column). When the database needs a new key, it takes the key from the row with the 1 in the identity column and then gets a new key ready for the next time.
When the database needs a new unique key, it finds the row with an identity column value of 1. This row's incrementor column value holds the next available key. The database uses this key and updates the table so a new key will be available next time.
Identity Column | Incrementor Column |
---|---|
3 | 78 |
4 | 3 |
1 | 65 |
2 | 12 |
After the next available key is fetched from the table, the table is updated for the next time a key is needed. The database can also retrieve multiple keys at once by taking the incrementor column value of more than one row.
In short, for automatic key generation to work, you need only have a key generation table set up with two columns: a primary key column for use as an identity column, and a column for storing the next available key. This table must be initialized with one record whose identity column value is 1 and whose incrementor column value is the first available key to use. Once you have this set up, you are ready to use automatic key generation.