Using sequences

In the previous chapter, we discussed two different methods for handling primary keys. Natural keys are key values that have actual meaning in the business model. They could include social security numbers or account numbers. However, our Companylink model uses synthetic keys—key values that are generated to maintain uniqueness. Using synthetic keys provides an easy way to guarantee uniqueness, although some object to the additional space required for non-business data. This is often a small price to pay for the usefulness of a synthetic key. Synthetic keys do, however, need to be generated. This can be done using programmatic techniques in the originating application. It can also be done using sequences. A sequence is a database object capable of generating sequential integers. These numbers can then be used as key values.

Using sequences to generate primary keys

Say that we want to be able to add new types of awards to the award table. In our last chapter, we placed a primary key on the award_id column, so we need to guarantee that only unique values are contained therein. One method to do this would be programmatic. A routine could be written that would look up the largest value in the table, increment it by one, and then insert the value. Although this method could work, there are at least two problems with it. First, if the table is heavily used, the rows would need to be locked to prevent another user session from doing the same thing a split-second later. It is possible that session #1 could query for the maximum value and that session #2 could do the same a moment after, but before session #1 inserts the value. In this scenario, both sessions increment the same value, but only one of them can insert it without causing a primary key violation. The second problem with the programmatic approach is performance. In order to use this method, many extra table scans must be performed to find the maximum value to increment. However, we can avoid both of these problems through the use of sequences.