Database Reference
In-Depth Information
What about the primary keys? The sample script that follows, again
taken from the SONGANDTRACK.SQL script, shows the INSERT com-
mand that created the primary key value for a row in the SONG table using
a sequence number. The song is “Soak up the Sun (Album Version)” by
Sheryl Crow. This unique identifier is the primary key column for the
SONG table. Incidentally, the SONG table contains a foreign key to the
ARTIST table in the SONG.ARTIST_ID column. The foreign key in the
following script is also highlighted and is selected using a subquery from the
ARTIST table:
INSERT INTO SONG(SONG_ID,ARTIST_ID,TITLE, PLAYING_TIME)
VALUES(
SONG_ID_SEQ.NEXTVAL
, (
SELECT ARTIST_ID FROM ARTIST WHERE NAME='Sheryl Crow'
)
, 'Soak Up The Sun (Album Version)','11:20');
22.1.3.3
Other Uses of Sequences
We have so far used sequences in SELECT statements, INSERT state-
ments, and subqueries. Sequences can be used in UPDATE statements, in a
similar fashion to that of INSERT statements, where a sequence already
inserted into a primary key column can be used to retrieve a description
column value based on an identifier value. For example, you have an order
system where a customer can enter a new mailing address when he or she
makes a new order (stored in the ORDERS table). Later, your database sys-
tem updates the master CUSTOMER table with new mailing addresses
from the ORDERS table.
UPDATE CUSTOMER SET LATEST_ADDRESS =
(SELECT MAILING_ADDRESS FROM ORDERS
WHERE ORDERS.CUST_ID = CUSTOMER.CUST_ID);
The CUST_ID column in the CUSTOMER table was originally
assigned using a sequence.
Another common use for sequences is more indirect than those already
mentioned. Sometimes it is useful to retrieve the NEXTVAL of a sequence
and use it to insert rows in two related tables (e.g., ARTIST and SONG).
When using PL/SQL code (see Chapter 24), you can place a sequence
number into a variable and use it within the PL/SQL code. Here is a sample
snippet of PL/SQL code, showing an INSERT command using a variable
for assigning the primary key (ID) in a table:
