Team Fly

Page 80

Sequences are objects in the database that can be used to provide sequentially generated integers. Without these valuable objects available to users, generating values sequentially would only be possible through the use of programs.

Sequences are generally created and named by a DBA. Among the attributes that can be defined when creating a sequence are a minimum value, a maximum value, a number to increment by and a number to start with. They are then made available to the systems applications and users that would need to generate them.

For the following example, we have established a cust_id_seq sequence, which increments by one each time it's called. When we created the sequence, we specified that 104501 should be the number to start with. For demonstration purposes, we'll use the DUAL table to select the next two sequence numbers. More often than not, an application will retrieve and assign the sequence numbers as records are inserted into the associated table.

SQL> select cust_id_seq.nextval
  2  from dual;

   NEXTVAL
----------
    104501

SQL> select cust_id_seq.nextval
  2  from dual;

   NEXTVAL
----------
    104502

CRITICAL SKILL 2.14
Employ Constraints: Linkage to Entity Models, Types, Deferred, Enforced, Gathering Exceptions

In our section on joins in this chapter, we introduced the concept of primary and foreign keys. These were, in fact, constraints on our tables. Constraints preserve the integrity of our database by enforcing business rules.

The primary key for the PROMOTIONS table in our sample schema is an integrity constraint. It requires that each value in promo_id be unique. Let's see what would happen if we tried to insert a row in this table with a promo_id value that already exists:

SQL> insert into promotions
  2    (promo_id,
  3     promo_name,

Team Fly