Database Reference
In-Depth Information
Figure 22.7
Initializing a
Sequence for a
Session Using
NEXTVAL.
22.1.3.2
Using Sequences in an INSERT Statement
Sequences can be used to generate primary and foreign keys in INSERT
statements. There is plenty of use of sequences in Appendix A, containing
the MUSIC schema generation scripts. Following is a sample of one of
those scripts called the SONGANDTRACK.SQL script file. This script
sample adds a single row to the CDTRACK table. The CDTRACK table
has two foreign keys: MUSICCD_ID and SONG_ID.
INSERT INTO CDTRACK(MUSICCD_ID,SONG_ID,TRACK_SEQ_NO) VALUES(
(
SELECT MUSICCD_ID FROM MUSICCD
WHERE TITLE='Soak Up the Sun'
)
, (
SELECT SONG_ID FROM SONG
WHERE TITLE='Soak Up The Sun (Album Version)'
)
, 1);
There are two subqueries in the previous script sample; each subquery
populates a foreign key column with the value of a primary key in another
table. The values in the primary key were originally generated from
sequences. The first subquery finds the MUSICCD_ID for the CD titled
“Soak up the Sun.” The second subquery selects the SONG_ID for the
song “Soak Up The Sun (Album Version)” by Sheryl Crow.
