Database Tutorial

There are several queries to write in this lab. They’re organized by the type of join and syntax pattern.

You should use the following formatting and query to verify completion of this step:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

SET NULL ''
COLUMN table_name   FORMAT A14
COLUMN column_id    FORMAT 9999
COLUMN column_name  FORMAT A22
COLUMN data_type    FORMAT A12
SELECT   table_name
,        column_id
,        column_name
,        CASE
           WHEN nullable = 'N' THEN 'NOT NULL'
           ELSE ''
         END AS nullable
,        CASE
           WHEN data_type IN ('CHAR','VARCHAR2','NUMBER') THEN
             data_type||'('||data_length||')'
           ELSE
             data_type
         END AS data_type
FROM     user_tab_columns
WHERE    table_name = 'RENTAL_ITEM'
ORDER BY 2;

It should display the following results:

TABLE_NAME     COLUMN_ID COLUMN_NAME            NULLABLE DATA_TYPE
-------------- --------- ---------------------- -------- ------------
RENTAL_ITEM            1 RENTAL_ITEM_ID         NOT NULL NUMBER(22)
RENTAL_ITEM            2 RENTAL_ID              NOT NULL NUMBER(22)
RENTAL_ITEM            3 ITEM_ID                NOT NULL NUMBER(22)
RENTAL_ITEM            4 CREATED_BY             NOT NULL NUMBER(22)
RENTAL_ITEM            5 CREATION_DATE          NOT NULL DATE
RENTAL_ITEM            6 LAST_UPDATED_BY        NOT NULL NUMBER(22)
RENTAL_ITEM            7 LAST_UPDATE_DATE       NOT NULL DATE
RENTAL_ITEM            8 RENTAL_ITEM_TYPE                NUMBER(22)
RENTAL_ITEM            9 RENTAL_ITEM_PRICE               NUMBER(22)
 
9 rows selected.

You should use the following formatting and query to verify completion of this step:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

SET NULL ''
COLUMN table_name   FORMAT A14
COLUMN column_id    FORMAT 9999
COLUMN column_name  FORMAT A22
COLUMN data_type    FORMAT A12
SELECT   table_name
,        column_id
,        column_name
,        CASE
           WHEN nullable = 'N' THEN 'NOT NULL'
           ELSE ''
         END AS nullable
,        CASE
           WHEN data_type IN ('CHAR','VARCHAR2','NUMBER') THEN
             data_type||'('||data_length||')'
           ELSE
             data_type
         END AS data_type
FROM     user_tab_columns
WHERE    table_name = 'PRICE'
ORDER BY 2;

It should display the following results:

TABLE_NAME     COLUMN_ID COLUMN_NAME            NULLABLE DATA_TYPE
-------------- --------- ---------------------- -------- ------------
PRICE                  1 PRICE_ID               NOT NULL NUMBER(22)
PRICE                  2 ITEM_ID                NOT NULL NUMBER(22)
PRICE                  3 PRICE_TYPE                      NUMBER(22)
PRICE                  4 ACTIVE_FLAG            NOT NULL VARCHAR2(1)
PRICE                  5 START_DATE             NOT NULL DATE
PRICE                  6 END_DATE                        DATE
PRICE                  7 AMOUNT                 NOT NULL NUMBER(22)
PRICE                  8 CREATED_BY             NOT NULL NUMBER(22)
PRICE                  9 CREATION_DATE          NOT NULL DATE
PRICE                 10 LAST_UPDATED_BY        NOT NULL NUMBER(22)
PRICE                 11 LAST_UPDATE_DATE       NOT NULL DATE
 
11 rows selected.

You should use the following formatting and query to verify completion of the constraint step:

1
2
3
4
5
6
7
8
9
10
11

COLUMN constraint_name   FORMAT A16
COLUMN search_condition  FORMAT A30
SELECT   uc.constraint_name
,        uc.search_condition
FROM     user_constraints uc INNER JOIN user_cons_columns ucc
ON       uc.table_name = ucc.table_name
AND      uc.constraint_name = ucc.constraint_name
WHERE    uc.table_name = UPPER('price')
AND      ucc.column_name = UPPER('active_flag')
AND      uc.constraint_name = UPPER('yn_price')
AND      uc.constraint_type = 'C';

It should display the following results:

CONSTRAINT_NAME      SEARCH_CONDITION_VC
-------------------- ------------------------------
YN_PRICE             active_flag IN ('Y','N')
 
1 row selected.

1. Rename the ITEM_RELEASE_DATE column of the ITEM table to RELEASE_DATE.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

SET NULL ''
COLUMN table_name   FORMAT A14
COLUMN column_id    FORMAT 9999
COLUMN column_name  FORMAT A22
COLUMN data_type    FORMAT A12
SELECT   table_name
,        column_id
,        column_name
,        CASE
           WHEN nullable = 'N' THEN 'NOT NULL'
           ELSE ''
         END AS nullable
,        CASE
           WHEN data_type IN ('CHAR','VARCHAR2','NUMBER') THEN
             data_type||'('||data_length||')'
           ELSE
             data_type
         END AS data_type
FROM     user_tab_columns
WHERE    table_name = 'ITEM'
ORDER BY 2;

TABLE_NAME     COLUMN_ID COLUMN_NAME            NULLABLE DATA_TYPE
-------------- --------- ---------------------- -------- ------------
ITEM                   1 ITEM_ID                NOT NULL NUMBER(22)
ITEM                   2 ITEM_BARCODE           NOT NULL VARCHAR2(14)
ITEM                   3 ITEM_TYPE              NOT NULL NUMBER(22)
ITEM                   4 ITEM_TITLE             NOT NULL VARCHAR2(60)
ITEM                   5 ITEM_SUBTITLE                   VARCHAR2(60)
ITEM                   6 ITEM_RATING            NOT NULL VARCHAR2(8)
ITEM                   7 RELEASE_DATE           NOT NULL DATE
ITEM                   8 CREATED_BY             NOT NULL NUMBER(22)
ITEM                   9 CREATION_DATE          NOT NULL DATE
ITEM                  10 LAST_UPDATED_BY        NOT NULL NUMBER(22)
ITEM                  11 LAST_UPDATE_DATE       NOT NULL DATE
 
11 rows selected.

2. Insert three new DVD releases into the ITEM table. You should insert a value in the RELEASE_DATE column that should always be less than 31 days during the course. You may want to check the syntax for inserting into the ITEM table, which is found in the lib2/seed/item_inserts.sql script.

   The easiest way to insert a value that is less than 31 days from today for your new rentals is:

   • A (TRUNC(SYSDATE) - 1) value for all RELEASE_DATE column values in the ITEM table.

1
2
3
4
5

SELECT   i.item_title
,        SYSDATE AS today
,        i.release_date
FROM     item i
WHERE   (SYSDATE - i.release_date) < 31;

                          RELEASE
ITEM_TITLE     TODAY      DATE
-------------- ---------- ----------
Tron           27-MAY-14  12-MAY-14
Ender's Game   27-MAY-14  12-MAY-14
Elysium        27-MAY-14  12-MAY-14
 
3 rows selected.

3. Insert a new row in the MEMBER table, and three new rows in the CONTACT, ADDRESS, STREET_ADDRESS, and TELEPHONE tables. The new contacts should be Harry, Ginny, and Lily Luna Potter. Please remember to check the seed_oracle_store.sql script’s logic for inserting to these tables because the right way is straightforward and the many wrong ways can make the lab extremely difficult.

   The insert statements follow a pattern. You start with the least dependent and work toward the most dependent. Typically, these would be built into a stored procedure and you would simply send all the values to it, and it would sequence the inserts into the different tables.

   You should use .NEXTVAL pseudo column calls for the primary key values and .CURRVAL pseudo column calls for the foreign key values. You should avoid entering literal values for primary and foreign key values unless you like troubleshooting your code because you will do a lot more of it with literal numeric values.

   The order of the INSERT statements should be like this, which follows the pattern introduced by lib2/seed/group_account3.sql script from Lab #4:

   1. Insert a new MEMBER row for the Potter family.
   2. Insert a new CONTACT row for Harry Potter.
   3. Insert a new ADDRESS row for Harry Potter.
   4. Insert a new STREET_ADDRESS row for Harry Potter.
   5. Insert a new TELEPHONE row for Harry Potter.
   6. Repeat steps 2 through 5 for Ginny Potter.
   7. Repeat steps 2 through 5 for Lily Potter.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

COLUMN account_number  FORMAT A10  HEADING "Account|Number"
COLUMN full_name       FORMAT A16  HEADING "Name|(Last, First MI)"
COLUMN street_address  FORMAT A14  HEADING "Street Address"
COLUMN city            FORMAT A10  HEADING "City"
COLUMN state           FORMAT A10  HEADING "State"
COLUMN postal_code     FORMAT A6   HEADING "Postal|Code"
SELECT   m.account_number
,        c.last_name || ', ' || c.first_name
||       CASE
           WHEN c.middle_name IS NOT NULL THEN
             ' ' || c.middle_name || ' '
         END AS full_name
,        sa.street_address
,        a.city
,        a.state_province AS state
,        a.postal_code
FROM     member m INNER JOIN contact c
ON       m.member_id = c.member_id INNER JOIN address a
ON       c.contact_id = a.contact_id INNER JOIN street_address sa
ON       a.address_id = sa.address_id INNER JOIN telephone t
ON       c.contact_id = t.contact_id
WHERE    c.last_name = 'Potter';

Account    Name 						     Postal
Number	   (Last, First MI)	Street Address City	  State      Code
---------- -------------------- -------------- ---------- ---------- ------
US00011    Potter, Harry	900 E, 300 N   Provo	  Utah	     84604
US00011    Potter, Ginny	900 E, 300 N   Provo	  Utah	     84604
US00011    Potter, Lily Luna	900 E, 300 N   Provo	  Utah	     84604
 
3 rows selected.

4. Insert three new rows in the RENTAL and four new rows in the RENTAL_ITEM tables. These rentals should be new releases, and it’s recommended that you use SYSDATE for their release date so that they work consistently through the course. Two of the rows in the RENTAL_ITEM table should link to the same row in the RENTAL table for the rental of Harry Potter.

   In other words, there you should insert data for: (a) Harry Potter renting two rental items for a 1-Day Rental period (one new release and one old release), (b) Ginny Potter renting one new release rental item for a 3-Day Rental period, and (c) Lily Luna Potter renting one new release rental item for a 5-Day Rental period.

   You should use .NEXTVAL pseudo column calls for the primary key values and .CURRVAL pseudo column calls for the foreign key values into RENTAL and RENTAL_ITEM tables respectively. Like entering the new customers, you should avoid entering literal values for primary and foreign key values.

   You should ensure the following for the insert into the RENTAL table:

   • A TRUNC(SYSDATE) value for all rentals in the CHECK_OUT_DATE column of the RENTAL table.
   • A null value in the RETURN_DATE column of the RENTAL table.

   You may want to check the syntax for inserting into the RENTAL and RENTAL_ITEM table, which is found in the lib2/seed/rental_inserts.sql script.

   The order of the INSERT statements should be like this:

   1. Insert a new RENTAL row for a new rental.
   2. Insert a one or more RENTAL_ITEM rows for any rental row.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

COLUMN full_name   FORMAT A18
COLUMN rental_id   FORMAT 9999
COLUMN rental_days FORMAT A14
COLUMN rentals     FORMAT 9999
COLUMN items       FORMAT 9999
SELECT   c.last_name||', '||c.first_name||' '||c.middle_name AS full_name
,        r.rental_id
,       (r.return_date - r.check_out_date) || '-DAY RENTAL' AS rental_days
,        COUNT(DISTINCT r.rental_id) AS rentals
,        COUNT(ri.rental_item_id) AS items
FROM     rental r INNER JOIN rental_item ri
ON       r.rental_id = ri.rental_id INNER JOIN contact c
ON       r.customer_id = c.contact_id
WHERE   (SYSDATE - r.check_out_date) < 15
AND      c.last_name = 'Potter'
GROUP BY c.last_name||', '||c.first_name||' '||c.middle_name
,        r.rental_id
,       (r.return_date - r.check_out_date) || '-DAY RENTAL'
ORDER BY 2;

FULL_NAME          RENTAL_ID RENTAL_DAYS    RENTALS ITEMS
------------------ --------- -------------- ------- -----
Potter, Harry           1006 1-DAY RENTAL         1     2
Potter, Ginny           1007 3-DAY RENTAL         1     1
Potter, Lily Luna       1008 5-DAY RENTAL         1     1
 
3 rows selected.

These business logic notes qualify the reasons why you’re changing the COMMON_LOOKUP table design and migrating existing foreign key values to new foreign key values.

Hint for Step #4

Before you shift contents, you should drop the COMMON_LOOKUP_N1 and COMMON_LOOKUP_U2 indexes. Then, you can shift the contents of one column to another column in the same row.

An UPDATE statement that moves the content from one column’s value to another for all rows in a table is:

1
2

UPDATE   common_lookup
SET      common_lookup_table = common_lookup_context;

The following shows you how UPDATE an existing row before you ALTER TABLE to DROP the column:

1
2
3
4

UPDATE   common_lookup
SET      common_lookup_table = 'ITEM'
,        common_lookup_column = 'ITEM_TYPE'
WHERE    common_lookup_context = 'ITEM';

The two rows containing the MULTIPLE context values, should migrate to four rows, as shown in the following table.

After you’ve made these data changes in the COMMON_LOOKUP table, you need to update the values in the TELEPHONE_TYPE and ADDRESS_TYPE columns. You make these changes with UPDATE statements.

You should UPDATE existing rows in the ADDRESS and TELEPHONE tables where the TELEPHONE_TYPE or ADDRESS_TYPE column values holds a foreign key value that matches the COMMON_LOOKUP_ID where the COMMON_LOOKUP_CONTEXT held a MULTIPLE column value. The simplest approach to updating these rows uses two UPDATE statements.

Implementation Steps:

1. Drop the COMMON_LOOKUP_N1 and COMMON_LOOKUP_U2 indexes.

1
2
3
4
5
6

COLUMN table_name FORMAT A14
COLUMN index_name FORMAT A20
SELECT   table_name
,        index_name
FROM     user_indexes
WHERE    table_name = 'COMMON_LOOKUP';

TABLE_NAME     INDEX_NAME
-------------- --------------------
COMMON_LOOKUP  PK_C_LOOKUP_1
 
1 row selected.

2. Add three new columns to the COMMON_LOOKUP table.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

SET NULL ''
COLUMN table_name   FORMAT A14
COLUMN column_id    FORMAT 9999
COLUMN column_name  FORMAT A22
COLUMN data_type    FORMAT A12
SELECT   table_name
,        column_id
,        column_name
,        CASE
           WHEN nullable = 'N' THEN 'NOT NULL'
           ELSE ''
         END AS nullable
,        CASE
           WHEN data_type IN ('CHAR','VARCHAR2','NUMBER') THEN
             data_type||'('||data_length||')'
           ELSE
             data_type
         END AS data_type
FROM     user_tab_columns
WHERE    table_name = 'COMMON_LOOKUP'
ORDER BY 2;

TABLE_NAME     COLUMN_ID COLUMN_NAME            NULLABLE DATA_TYPE
-------------- --------- ---------------------- -------- ------------
COMMON_LOOKUP          1 COMMON_LOOKUP_ID       NOT NULL NUMBER(22)
COMMON_LOOKUP          2 COMMON_LOOKUP_CONTEXT  NOT NULL VARCHAR2(30)
COMMON_LOOKUP          3 COMMON_LOOKUP_TYPE     NOT NULL VARCHAR2(30)
COMMON_LOOKUP          4 COMMON_LOOKUP_MEANING  NOT NULL VARCHAR2(30)
COMMON_LOOKUP          5 CREATED_BY             NOT NULL NUMBER(22)
COMMON_LOOKUP          6 CREATION_DATE          NOT NULL DATE
COMMON_LOOKUP          7 LAST_UPDATED_BY        NOT NULL NUMBER(22)
COMMON_LOOKUP          8 LAST_UPDATE_DATE       NOT NULL DATE
COMMON_LOOKUP          9 COMMON_LOOKUP_TABLE             VARCHAR2(30)
COMMON_LOOKUP         10 COMMON_LOOKUP_COLUMN            VARCHAR2(30)
COMMON_LOOKUP         11 COMMON_LOOKUP_CODE              VARCHAR2(30)
 
11 rows selected.

3. Migrate data and populate (or seed) new columns with existing data.

4. Add two new rows to the COMMON_LOOKUP table to support the 'HOME' and 'WORK' possibilities for the TELEPHONE_TYPE column. The following shows you what to insert into the COMMON_LOOKUP table:

   Table Name: COMMON_LOOKUP
   Table	Column	Code	Type	Meaning
   TEELEPHONE	TELEPHONE_TYPE		HOME	Home
   TELEPHONE	TELEPHONE_TYPE		WORK	Work;

   You should insert the required two rows by using the following named notation signature for the INSERT statement:

   INSERT INTO common_lookup ( common_lookup_id , common_lookup_table , common_lookup_column , common_lookup_type , common_lookup_meaning , created_by , creation_date , last_updated_by , last_update_date ) VALUES ( ... );

   INSERT INTO common_lookup ( common_lookup_id , common_lookup_table , common_lookup_column , common_lookup_type , common_lookup_meaning , created_by , creation_date , last_updated_by , last_update_date ) VALUES ( ... );

   

   The COMMON_LOOKUP table should look like the following after you insert two new rows in the table. You can click on the Detail Verification Scripts link to see the verification query and its results. You can click on the Detail Verification Scripts link to see the verification query and its results.

   Detail Verification Scripts →

   You can use the following query to verify the contents of the COMMON_LOOKUP table.

   COLUMN common_lookup_context FORMAT A14 HEADING "Common|Lookup Context" COLUMN common_lookup_table FORMAT A12 HEADING "Common|Lookup Table" COLUMN common_lookup_column FORMAT A18 HEADING "Common|Lookup Column" COLUMN common_lookup_type FORMAT A18 HEADING "Common|Lookup Type" SELECT common_lookup_context , common_lookup_table , common_lookup_column , common_lookup_type FROM common_lookup WHERE common_lookup_table IN (SELECT table_name FROM user_tables) ORDER BY 1, 2, 3;

   COLUMN common_lookup_context FORMAT A14 HEADING "Common|Lookup Context" COLUMN common_lookup_table FORMAT A12 HEADING "Common|Lookup Table" COLUMN common_lookup_column FORMAT A18 HEADING "Common|Lookup Column" COLUMN common_lookup_type FORMAT A18 HEADING "Common|Lookup Type" SELECT common_lookup_context , common_lookup_table , common_lookup_column , common_lookup_type FROM common_lookup WHERE common_lookup_table IN (SELECT table_name FROM user_tables) ORDER BY 1, 2, 3;

   The query should return the following:

   Common Common Common Common Lookup Context Lookup Table Lookup Column Lookup Type -------------- ------------ ------------------ ------------------ CONTACT CONTACT CONTACT_TYPE EMPLOYEE CONTACT CONTACT CONTACT_TYPE CUSTOMER ITEM ITEM ITEM_TYPE DVD_WIDE_SCREEN ITEM ITEM ITEM_TYPE NINTENDO_GAMECUBE ITEM ITEM ITEM_TYPE PLAYSTATION2 ITEM ITEM ITEM_TYPE XBOX ITEM ITEM ITEM_TYPE BLU-RAY ITEM ITEM ITEM_TYPE DVD_FULL_SCREEN MEMBER MEMBER MEMBER_TYPE VISA_CARD MEMBER MEMBER MEMBER_TYPE MASTER_CARD MEMBER MEMBER MEMBER_TYPE DISCOVER_CARD MEMBER MEMBER MEMBER_TYPE GROUP MEMBER MEMBER MEMBER_TYPE INDIVIDUAL MULTIPLE ADDRESS ADDRESS_TYPE WORK MULTIPLE ADDRESS ADDRESS_TYPE HOME SYSTEM_USER SYSTEM_USER SYSTEM_USER_TYPE INDIVIDUAL SYSTEM_USER SYSTEM_USER SYSTEM_USER_TYPE COST_CENTER SYSTEM_USER SYSTEM_USER SYSTEM_USER_TYPE SYSTEM_GROUP SYSTEM_USER SYSTEM_USER SYSTEM_USER_TYPE SYSTEM_ADMIN SYSTEM_USER SYSTEM_USER SYSTEM_USER_TYPE DBA TELEPHONE TELEPHONE_TYPE HOME TELEPHONE TELEPHONE_TYPE WORK

   Common Common Common Common Lookup Context Lookup Table Lookup Column Lookup Type -------------- ------------ ------------------ ------------------ CONTACT CONTACT CONTACT_TYPE EMPLOYEE CONTACT CONTACT CONTACT_TYPE CUSTOMER ITEM ITEM ITEM_TYPE DVD_WIDE_SCREEN ITEM ITEM ITEM_TYPE NINTENDO_GAMECUBE ITEM ITEM ITEM_TYPE PLAYSTATION2 ITEM ITEM ITEM_TYPE XBOX ITEM ITEM ITEM_TYPE BLU-RAY ITEM ITEM ITEM_TYPE DVD_FULL_SCREEN MEMBER MEMBER MEMBER_TYPE VISA_CARD MEMBER MEMBER MEMBER_TYPE MASTER_CARD MEMBER MEMBER MEMBER_TYPE DISCOVER_CARD MEMBER MEMBER MEMBER_TYPE GROUP MEMBER MEMBER MEMBER_TYPE INDIVIDUAL MULTIPLE ADDRESS ADDRESS_TYPE WORK MULTIPLE ADDRESS ADDRESS_TYPE HOME SYSTEM_USER SYSTEM_USER SYSTEM_USER_TYPE INDIVIDUAL SYSTEM_USER SYSTEM_USER SYSTEM_USER_TYPE COST_CENTER SYSTEM_USER SYSTEM_USER SYSTEM_USER_TYPE SYSTEM_GROUP SYSTEM_USER SYSTEM_USER SYSTEM_USER_TYPE SYSTEM_ADMIN SYSTEM_USER SYSTEM_USER SYSTEM_USER_TYPE DBA TELEPHONE TELEPHONE_TYPE HOME TELEPHONE TELEPHONE_TYPE WORK

5. At this point, you need to fix the COMMON_LOOKUP table’s structure by doing the following four things to the table.
1. You must get rid of old and now obsolete COMMON_LOOKUP_CONTEXT column.
2. Add a NOT NULL constraint to the COMMON_LOOKUP_TABLE column.
3. Add a NOT NULL constraint to the COMMON_LOOKUP_COLUMN column.
4. Add a unique index using the following three columns:
   • COMMON_LOOKUP_TABLE
   • COMMON_LOOKUP_COLUMN
   • COMMON_LOOKUP_TYPE

Detail Verification Scripts →

You can verify the new structure of the COMMON_LOOKUP table with the following query:

SET NULL '' COLUMN table_name FORMAT A14 COLUMN column_id FORMAT 9999 COLUMN column_name FORMAT A22 COLUMN data_type FORMAT A12 SELECT table_name , column_id , column_name , CASE WHEN nullable = 'N' THEN 'NOT NULL' ELSE '' END AS nullable , CASE WHEN data_type IN ('CHAR','VARCHAR2','NUMBER') THEN data_type||'('||data_length||')' ELSE data_type END AS data_type FROM user_tab_columns WHERE table_name = 'COMMON_LOOKUP' ORDER BY 2;

SET NULL '' COLUMN table_name FORMAT A14 COLUMN column_id FORMAT 9999 COLUMN column_name FORMAT A22 COLUMN data_type FORMAT A12 SELECT table_name , column_id , column_name , CASE WHEN nullable = 'N' THEN 'NOT NULL' ELSE '' END AS nullable , CASE WHEN data_type IN ('CHAR','VARCHAR2','NUMBER') THEN data_type||'('||data_length||')' ELSE data_type END AS data_type FROM user_tab_columns WHERE table_name = 'COMMON_LOOKUP' ORDER BY 2;

The query should return the following description of the COMMON_LOOKUP table:

Column Table Name COLUMN_ID Name NULLABLE DATA_TYPE -------------- --------- ---------------------- -------- ------------ COMMON_LOOKUP 1 COMMON_LOOKUP_ID NOT NULL NUMBER(22) COMMON_LOOKUP 2 COMMON_LOOKUP_TYPE NOT NULL VARCHAR2(30) COMMON_LOOKUP 3 COMMON_LOOKUP_MEANING NOT NULL VARCHAR2(30) COMMON_LOOKUP 4 CREATED_BY NOT NULL NUMBER(22) COMMON_LOOKUP 5 CREATION_DATE NOT NULL DATE COMMON_LOOKUP 6 LAST_UPDATED_BY NOT NULL NUMBER(22) COMMON_LOOKUP 7 LAST_UPDATE_DATE NOT NULL DATE COMMON_LOOKUP 8 COMMON_LOOKUP_TABLE NOT NULL VARCHAR2(30) COMMON_LOOKUP 9 COMMON_LOOKUP_COLUMN NOT NULL VARCHAR2(30) COMMON_LOOKUP 10 COMMON_LOOKUP_CODE VARCHAR2(30)   10 rows selected.

Column Table Name COLUMN_ID Name NULLABLE DATA_TYPE -------------- --------- ---------------------- -------- ------------ COMMON_LOOKUP 1 COMMON_LOOKUP_ID NOT NULL NUMBER(22) COMMON_LOOKUP 2 COMMON_LOOKUP_TYPE NOT NULL VARCHAR2(30) COMMON_LOOKUP 3 COMMON_LOOKUP_MEANING NOT NULL VARCHAR2(30) COMMON_LOOKUP 4 CREATED_BY NOT NULL NUMBER(22) COMMON_LOOKUP 5 CREATION_DATE NOT NULL DATE COMMON_LOOKUP 6 LAST_UPDATED_BY NOT NULL NUMBER(22) COMMON_LOOKUP 7 LAST_UPDATE_DATE NOT NULL DATE COMMON_LOOKUP 8 COMMON_LOOKUP_TABLE NOT NULL VARCHAR2(30) COMMON_LOOKUP 9 COMMON_LOOKUP_COLUMN NOT NULL VARCHAR2(30) COMMON_LOOKUP 10 COMMON_LOOKUP_CODE VARCHAR2(30) 10 rows selected.

You can verify the properly named NOT NULL constraints with the following query:

COLUMN constraint_name FORMAT A22 HEADING "Constraint Name" COLUMN search_condition FORMAT A36 HEADING "Search Condition" COLUMN constraint_type FORMAT A10 HEADING "Constraint|Type" SELECT uc.constraint_name , uc.search_condition , uc.constraint_type FROM user_constraints uc INNER JOIN user_cons_columns ucc ON uc.table_name = ucc.table_name AND uc.constraint_name = ucc.constraint_name WHERE uc.table_name = UPPER('common_lookup') AND uc.constraint_type IN (UPPER('c'),UPPER('p')) ORDER BY uc.constraint_type DESC , uc.constraint_name;

COLUMN constraint_name FORMAT A22 HEADING "Constraint Name" COLUMN search_condition FORMAT A36 HEADING "Search Condition" COLUMN constraint_type FORMAT A10 HEADING "Constraint|Type" SELECT uc.constraint_name , uc.search_condition , uc.constraint_type FROM user_constraints uc INNER JOIN user_cons_columns ucc ON uc.table_name = ucc.table_name AND uc.constraint_name = ucc.constraint_name WHERE uc.table_name = UPPER('common_lookup') AND uc.constraint_type IN (UPPER('c'),UPPER('p')) ORDER BY uc.constraint_type DESC , uc.constraint_name;

The query should return the following description of the COMMON_LOOKUP table:

Constraint Constraint Name Search Condition Type ---------------------- ------------------------------------ ---------- PK_CLOOKUP_1 P NN_CLOOKUP_2 "COMMON_LOOKUP_TYPE" IS NOT NULL C NN_CLOOKUP_3 "COMMON_LOOKUP_MEANING" IS NOT NULL C NN_CLOOKUP_4 "CREATED_BY" IS NOT NULL C NN_CLOOKUP_5 "CREATION_DATE" IS NOT NULL C NN_CLOOKUP_6 "LAST_UPDATED_BY" IS NOT NULL C NN_CLOOKUP_7 "LAST_UPDATE_DATE" IS NOT NULL C NN_CLOOKUP_8 "COMMON_LOOKUP_TABLE" IS NOT NULL C NN_CLOOKUP_9 "COMMON_LOOKUP_COLUMN" IS NOT NULL C   9 rows selected.

Constraint Constraint Name Search Condition Type ---------------------- ------------------------------------ ---------- PK_CLOOKUP_1 P NN_CLOOKUP_2 "COMMON_LOOKUP_TYPE" IS NOT NULL C NN_CLOOKUP_3 "COMMON_LOOKUP_MEANING" IS NOT NULL C NN_CLOOKUP_4 "CREATED_BY" IS NOT NULL C NN_CLOOKUP_5 "CREATION_DATE" IS NOT NULL C NN_CLOOKUP_6 "LAST_UPDATED_BY" IS NOT NULL C NN_CLOOKUP_7 "LAST_UPDATE_DATE" IS NOT NULL C NN_CLOOKUP_8 "COMMON_LOOKUP_TABLE" IS NOT NULL C NN_CLOOKUP_9 "COMMON_LOOKUP_COLUMN" IS NOT NULL C 9 rows selected.

You can verify the COMMON_LOOKUP table’s new unique natural key index with the following query:

COLUMN sequence_name FORMAT A22 HEADING "Sequence Name" COLUMN column_position FORMAT 999 HEADING "Column|Position" COLUMN column_name FORMAT A22 HEADING "Column|Name" SELECT UI.index_name , uic.column_position , uic.column_name FROM user_indexes UI INNER JOIN user_ind_columns uic ON UI.index_name = uic.index_name AND UI.table_name = uic.table_name WHERE UI.table_name = UPPER('common_lookup') ORDER BY UI.index_name , uic.column_position;

COLUMN sequence_name FORMAT A22 HEADING "Sequence Name" COLUMN column_position FORMAT 999 HEADING "Column|Position" COLUMN column_name FORMAT A22 HEADING "Column|Name" SELECT ui.index_name , uic.column_position , uic.column_name FROM user_indexes ui INNER JOIN user_ind_columns uic ON ui.index_name = uic.index_name AND ui.table_name = uic.table_name WHERE ui.table_name = UPPER('common_lookup') ORDER BY ui.index_name , uic.column_position;

The query should return the following description for the COMMON_LOOKUP table’s natural key index:

Column Column Index Name Position Name -------------------- -------- ---------------------- CLOOKUP_U1 1 COMMON_LOOKUP_TABLE CLOOKUP_U1 2 COMMON_LOOKUP_COLUMN CLOOKUP_U1 3 COMMON_LOOKUP_TYPE PK_CLOOKUP_1 1 COMMON_LOOKUP_ID

Column Column Index Name Position Name -------------------- -------- ---------------------- CLOOKUP_U1 1 COMMON_LOOKUP_TABLE CLOOKUP_U1 2 COMMON_LOOKUP_COLUMN CLOOKUP_U1 3 COMMON_LOOKUP_TYPE PK_CLOOKUP_1 1 COMMON_LOOKUP_ID

6. The last step requires that you update incorrect foreign key values in the telephone table. They are invalid because the rows in the COMMON_LOOKUP table that once supported both the ADDRESS and TELEPHONE tables now only supports the ADDRESS table. You need to copy the values of the correct COMMON_LOOKUP_ID column values (the surrogate primary key column) into the TELEPHONE_TYPE column of the TELEPHONE table.

   You can find the obsolete values with a query like this:

   SELECT common_lookup_id FROM common_lookup WHERE common_lookup_table = 'ADDRESS' AND common_lookup_type = 'HOME';

   SELECT common_lookup_id FROM common_lookup WHERE common_lookup_table = 'ADDRESS' AND common_lookup_type = 'HOME';

   You can find the correct values with a query like this:

   SELECT common_lookup_id FROM common_lookup WHERE common_lookup_table = 'TELEPHONE' AND common_lookup_type = 'HOME';

   SELECT common_lookup_id FROM common_lookup WHERE common_lookup_table = 'TELEPHONE' AND common_lookup_type = 'HOME';

   You should structure and UPDATE statement that sets the TELEPHONE_TYPE column values equal to the correct values where they are currently the obsolete values.

   Detail Verification Scripts →

   COLUMN common_lookup_table FORMAT A14 HEADING "Common|Lookup Table" COLUMN common_lookup_column FORMAT A14 HEADING "Common|Lookup Column" COLUMN common_lookup_type FORMAT A8 HEADING "Common|Lookup|Type" COLUMN count_dependent FORMAT 999 HEADING "Count of|Foreign|Keys" COLUMN count_lookup FORMAT 999 HEADING "Count of|Primary|Keys" SELECT cl.common_lookup_table , cl.common_lookup_column , cl.common_lookup_type , COUNT(a.address_id) AS count_dependent , COUNT(DISTINCT cl.common_lookup_table) AS count_lookup FROM address a RIGHT JOIN common_lookup cl ON a.address_type = cl.common_lookup_id WHERE cl.common_lookup_table = 'ADDRESS' AND cl.common_lookup_column = 'ADDRESS_TYPE' AND cl.common_lookup_type IN ('HOME','WORK') GROUP BY cl.common_lookup_table , cl.common_lookup_column , cl.common_lookup_type UNION SELECT cl.common_lookup_table , cl.common_lookup_column , cl.common_lookup_type , COUNT(t.telephone_id) AS count_dependent , COUNT(DISTINCT cl.common_lookup_table) AS count_lookup FROM telephone t RIGHT JOIN common_lookup cl ON t.telephone_type = cl.common_lookup_id WHERE cl.common_lookup_table = 'TELEPHONE' AND cl.common_lookup_column = 'TELEPHONE_TYPE' AND cl.common_lookup_type IN ('HOME','WORK') GROUP BY cl.common_lookup_table , cl.common_lookup_column , cl.common_lookup_type;

   COLUMN common_lookup_table FORMAT A14 HEADING "Common|Lookup Table" COLUMN common_lookup_column FORMAT A14 HEADING "Common|Lookup Column" COLUMN common_lookup_type FORMAT A8 HEADING "Common|Lookup|Type" COLUMN count_dependent FORMAT 999 HEADING "Count of|Foreign|Keys" COLUMN count_lookup FORMAT 999 HEADING "Count of|Primary|Keys" SELECT cl.common_lookup_table , cl.common_lookup_column , cl.common_lookup_type , COUNT(a.address_id) AS count_dependent , COUNT(DISTINCT cl.common_lookup_table) AS count_lookup FROM address a RIGHT JOIN common_lookup cl ON a.address_type = cl.common_lookup_id WHERE cl.common_lookup_table = 'ADDRESS' AND cl.common_lookup_column = 'ADDRESS_TYPE' AND cl.common_lookup_type IN ('HOME','WORK') GROUP BY cl.common_lookup_table , cl.common_lookup_column , cl.common_lookup_type UNION SELECT cl.common_lookup_table , cl.common_lookup_column , cl.common_lookup_type , COUNT(t.telephone_id) AS count_dependent , COUNT(DISTINCT cl.common_lookup_table) AS count_lookup FROM telephone t RIGHT JOIN common_lookup cl ON t.telephone_type = cl.common_lookup_id WHERE cl.common_lookup_table = 'TELEPHONE' AND cl.common_lookup_column = 'TELEPHONE_TYPE' AND cl.common_lookup_type IN ('HOME','WORK') GROUP BY cl.common_lookup_table , cl.common_lookup_column , cl.common_lookup_type;

   The results should be the following:

   Common Count of Count of Common Common Lookup Foreign Primary Lookup Table Lookup Column Type Keys Keys -------------- -------------- -------- -------- -------- ADDRESS ADDRESS_TYPE HOME 15 1 ADDRESS ADDRESS_TYPE WORK 0 1 TELEPHONE TELEPHONE_TYPE HOME 15 1 TELEPHONE TELEPHONE_TYPE WORK 0 1   4 rows selected.

   Common Count of Count of Common Common Lookup Foreign Primary Lookup Table Lookup Column Type Keys Keys -------------- -------------- -------- -------- -------- ADDRESS ADDRESS_TYPE HOME 15 1 ADDRESS ADDRESS_TYPE WORK 0 1 TELEPHONE TELEPHONE_TYPE HOME 15 1 TELEPHONE TELEPHONE_TYPE WORK 0 1 4 rows selected.