DDL: Sequences

Synthetic keys are very common in relational databases. They are particularly useful

when there is no combination of attributes that can guarantee a unique value
to avoid composite primary/foreign keys
if there is a risk that non-unique values will appear in the future
if there is a risk that the values of a natural key might change

Most relational database platforms provide a mechanism for automatically generating new unique values for synthetic keys. In Microsoft database systems this is done simply by activating a property of a column. Oracle however provides a special database object called a sequence which must be explicitly referenced when a new value is required. This approach is slightly more complex but provides greater flexibility.

A sequence for generating new matriculation numbers can be created using a statement such as the following which illustrates the most commonly used features:

    CREATE SEQUENCE matric_seq
           START WITH 4000000
           INCREMENT BY 1
           NOCACHE
           NOCYCLE;

START WITH and INCREMENT BY are fairly self-explanatory. For busy systems, Oracle allows sequence values to be cached in memory for faster access. However this can lead to missing numbers in the sequence of values actually used. The example statement specifies that no values should be cached. It also specifies that the sequence should continue indefinitely with the NOCYCLE flag. The alternative would be to generate a repeating sequence of numbers.

Each Oracle sequence provides two attributes, NEXTVAL and CURRVAL. If MATRIC_SEQ.NEXTVAL is referenced in a data insertion statement, a new value is generated and the sequence is incremented. On the other hand, MATRIC_NO.CURRVAL gives the most recent value generated, but the sequence is not incremented when this attribute is referenced. This can be very useful, for example when inserting a series of related records in a second table. CURRVAL can be used to find the appropriate value to use for the foreign key.

Sequences can be dropped using the DROP SEQUENCE statement, and they can be altered with ALTER SEQUENCE. Altering a sequence can be important for example when you first import some data into a table that has a synthetic key. It will be important to update the START WITH value so that the next row inserted into the table takes the next value in series.

The complete description of the CREATE SEQUENCE statement can be found in the Oracle documentation. The syntax to use when using sequence values in newly inserted rows of data will be described next week under the topic of data maintenance.

DDL: Sequences

Further reading