Database Tutorial

Learning Outcomes

• Learn how to create an unconstrained table.
• Learn how unconstrained tables fail to support data integrity.

Supporting Materials

The following web pages may help you understand the SQL syntax in this tutorial:

• CREATE statement
• INSERT statement
• SELECT Clause

Lesson Materials

An unconstrained table acts like a list of a record structure but you don’t need to insert any column values. An unconstrained table doesn’t guarantee that insertions or updates of column values will comply with business rules.

For example, you can create an unconstrained new_hire table with the following CREATE TABLE statement:

CREATE TABLE new_hire
( new_hire_id  NUMBER
, first_name   VARCHAR2(20)
, middle_name  VARCHAR2(20)
, last_name    VARCHAR2(20)
, hire_date    DATE );

You will see the following when you describe the the new_hire table from the SQL*Plus prompt:

Name              Null?    Type
----------------- -------- ----------------------------
NEW_HIRE_ID                NUMBER
FIRST_NAME                 VARCHAR2(20)
MIDDLE_NAME                VARCHAR2(20)
LAST_NAME                  VARCHAR2(20)
HIRE_DATE                  DATE

You can create a new_hire_s sequence to enter automatic numbers, which you will learn more about in next week’s lesson. For the moment, you reference a sequence name with a dot or period (.) and the keyword nextval. The .nextval is a pseudo column.

A .nextval pseudo column allows you to reference the next value of a sequence. You can also use the .currval pseudo column to access the last sequence value.

You can enter any type of data into an unconstrained table. You can enter a null value or a real value. The INSERT statement with a VALUES clause has two sets of comma delimited values inside parentheses.

The first set of comma delimited values in parentheses is optional unless you want to insert fewer than all the column values defined in the table. The first set of parentheses should only contain comma delimited column names.

The second set of comma delimited values in parentheses is always required. You must have a value for all values defined in the table’s record structure unless you have provided the optional comma delimited list of column names. You only need to provide values for the columns you list in the first set of parentheses.

The following INSERT statement enters a complete set of data values by excluding the optional list of column values:

INSERT INTO new_hire
VALUES
( new_hire_s.NEXTVAL
,'Malcolm'
,'Jacob'
,'Lewis'
,'14-FEB-2018');

The next INSERT statement uses the optional first set of comma delimited column names. It provides a complete list of column names, which means that it works the same way as the prior INSERT statement. At least, it will insert values into all of the columns in the table. They will be inserted in the same order as the list of column names.

The difference between the two statements is that the first one used the .nextval pseudo column to get the next value from a sequence. The second INSERT statement used the .currval pseudo column, which uses the last value from a sequence.

INSERT INTO new_hire
( new_hire_id
, first_name
, middle_name
, last_name
, hire_date )
VALUES
( new_hire_s.CURRVAL
,'Malcolm'
,'Jacob'
,'Lewis'
,'14-FEB-2018');

You can also enter an incomplete set of data by listing only a subset of comma delimited columns in the first set of parentheses. You only need to put comma delimited values in the second set of parentheses that map to the list of comma delimited column names.

Like the first INSERT statement, this uses a .nextval pseudo column for the new_hire_id column. The following only inserts values for the new_hire_id, first_name, and last_name columns. By default, an INSERT statement adds null values to any columns not explicitly qualified in the comma delimited list of column names.

INSERT INTO new_hire
( new_hire_id
, first_name
, last_name )
VALUES
( new_hire_s.NEXTVAL
,'Henry'
,'Chabot' );

This last INSERT statement uses only one column, which is the minimum number required. The following statement adds a row with all null values:

INSERT INTO new_hire
( new_hire_id )
VALUES
( NULL );

You can format and query the inserted rows with the following statement. The SQL*Plus formatting commands will define the width of the displayed columns.

SET NULL '<Null>'
 
COLUMN new_hire_id  FORMAT 99999  HEADING "New|Hire|ID #"
COLUMN first_name   FORMAT A10    HEADING "First|Name"
COLUMN middle_name  FORMAT A10    HEADING "Middle|Name"
COLUMN last_name    FORMAT A10    HEADING "Last|Name"
COLUMN hire_date    FORMAT A10    HEADING "Hire|Date"
 
SELECT *
FROM   new_hire;

It should print the following output:

   New
  Hire First      Middle     Last       Hire
  ID # Name       Name       Name       Date
------ ---------- ---------- ---------- ----------
     1 Malcolm    Jacob      Lewis      14-FEB-18
     1 Malcolm    Jacob      Lewis      14-FEB-18
     2 Henry      <Null>     Chabot     <Null>
     3 <Null>     <Null>     <Null>     <Null>
<Null> <Null>     <Null>     <Null>     <Null>

You should be able to see that not all of the rows in the table contain meaningful data. In fact, you should see:

• The first two rows are duplicates of each other, which violates the concept that every row in a table should be unique.
• The third row lacks a hire_date value, which would be a required element of each row.
• The fourth row is empty with null values in any table’s columns.

Unconstrained tables allow you to insert incomplete or empty rows into tables. It means that if you implement unconstrained tables, you must develop a thorough and complete Application Programming Interface (API) that guarantees how data will be inserted into or updated in the table.

Support Scripts

You can find support scripts for this exercise, as noted below.

/home/student/Data/cit225/oracle/lab2/support/unconstrained.sql