Database Tutorial

A dependent INSERT statement occurs when data exists in other tables or when another INSERT statement should precede an INSERT statement. This tutorial shows you both examples.

Dependent on Other Data

The INSERT statement into the MEMBER table requires information that already exists in the SYSTEM_USER and COMMON_LOOKUP tables. While you could query the data and then type it into the INSERT statement’s VALUES clause, it is much easier to use a scalar subquery to find the primary key values.

A subquery acts like a function where the return value is the column in the SELECT list and the parameters are the comparisons made in the WHERE clause. The parameters are the human friendly text values that let us find a unique row, which means they’re the values of the table’s natural key. The SELECT list returns the surrogate key value, which is also the primary key of the table.

The following inserts values into the MEMBER table. You should note that the INSERT statement includes an override signature or list of column names. That list ensures you can match the elements in the VALUES clause to the right column values.

The member_id column is the surrogate key column. The member_type, credit_card_type, created_by, and last_updated_by columns hold foreign key values that match up with rows in other tables.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

INSERT INTO member
( member_id
, member_type
, account_number
, credit_card_number
, credit_card_type
, created_by
, creation_date
, last_updated_by
, last_update_date )
VALUES
( member_s1.NEXTVAL                               -- member_id
, NULL                                            -- member_type
,'B293-71445'                                     -- account_number
,'1111-2222-3333-4444'                            -- credit_card_number
,(SELECT   common_lookup_id
  FROM     common_lookup
  WHERE    common_lookup_context = 'MEMBER'
  AND      common_lookup_type = 'DISCOVER_CARD')  -- credit_card_type
,(SELECT   system_user_id
  FROM     system_user
  WHERE    system_user_name = 'SYSADMIN')         -- created_by
, SYSDATE                                         -- creation_date
,(SELECT   system_user_id
  FROM     system_user
  WHERE    system_user_name = 'SYSADMIN')         -- last_updated_by
, SYSDATE                                         -- last_update_date
);

Line 12 includes a call to the member_s1 sequence value by using the .nextval pseuodcolumn, which accesses the next value of a numeric sequence that increments one value at a time. The member_type column is currently a nullable or optional column and line 13 passes a null value. Lines 16 through 19 query the common_lookup table to find and return a copy of a primary key value. Lines 20 through 22 and lines 24 through 26 query the system_user table to find and return a copy of a primary key value. All of these subqueries are scalar subqueries, which mean they return one column value from one row in the table.

The next INSERT statement into the CONTACT table is dependent on two things. The values from its subqueries and the last value of the member_s1 sequence value. The dependency on the last value of the member_s1 sequence value means that you must use this INSERT statement before you insert another row into the MEMBER table in the current session.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

INSERT INTO contact
( contact_id
, member_id
, contact_type
, first_name
, last_name
, created_by
, creation_date
, last_updated_by
, last_update_date )
VALUES
( contact_s1.NEXTVAL                              -- contact_id
, member_s1.CURRVAL                               -- member_id
,(SELECT   common_lookup_id
  FROM     common_lookup
  WHERE    common_lookup_context = 'CONTACT'
  AND      common_lookup_type = 'CUSTOMER')       -- contact_type
,'Randi'                                          -- first_name
,'Winn'                                           -- last_name
,(SELECT   system_user_id
  FROM     system_user
  WHERE    system_user_name = 'SYSADMIN')         -- created_by
, SYSDATE                                         -- creation_date
,(SELECT   system_user_id
  FROM     system_user
  WHERE    system_user_name = 'SYSADMIN')         -- last_updated_by
, SYSDATE                                         -- last_update_date
);

Line 12 includes, by coincidence, a call to the contact_s1 sequence value, which accesses the next value of a numeric sequence that increments one value at a time. Line 13 is different because it inserts the last value consumed from the sequence by using the .currval pseudocolumn, which is a copy of the primary key value you inserted into the MEMBER table. The subqueries employ the same logic as the prior INSERT statement to the MEMBER table.