Database Tutorial

Learning Outcomes

• Learn how to create an constrained table.
• Learn how constrained tables 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
• NOT NULL Constraint
• UNIQUE Constraint
• PRIMARY KEY Constraint

Lesson Materials

Like an unconstrained table, a constrained table acts like a list of a record structure. The difference between the unconstrained and constrained table definitions is that the latter includes constraints. Constraints determine when you must enter a column value to create or update a row. Constraints also may govern the range of values you insert or update, and whether certain various columns in the table are unique.

You may use these constraints to impose business rules on how you insert or update data. Constraints are critical components that support the design and construction of tables.

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

CREATE TABLE new_hire
( new_hire_id  NUMBER        CONSTRAINT new_hire_pk  PRIMARY KEY
, first_name   VARCHAR2(20)  CONSTRAINT new_hire_nn1 NOT NULL
, middle_name  VARCHAR2(20)
, last_name    VARCHAR2(20)  CONSTRAINT new_hire_nn2 NOT NULL
, hire_date    DATE          CONSTRAINT new_hire_nn3 NOT NULL);

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

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

The new_hire_id, first_name, last_name, and hire_date columns are now constrained. The NOT NULL constraint makes the columns mandatory rather than optional. A mandatory column means you must insert a column value when you create a row or when you update a row.

The .nextval and .currval pseudo columns let you control automatic numbering in a table. Please check the Unconstrained Table article for more discussion of how they work in an Oracle database.

You must enter values into all mandatory columns. The means you only have two approaches that work with the newly constrained new_hire table. You can accept the data dictionary default column list or you can provide a column list of all mandatory columns. Naturally, you must have values that match the default column list or match your customized mandatory column list.

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');

The INSERT statement that uses the new_hire_s.currval pseudo column worked with an unconstrained table but now fails because the new_hire_id column is now constrained as a primary key. Primary key constraints placed on a single column ensure that column is mandatory and unique. This means a primary key constraint implements a NOT NULL column constraint and a UNIQUE table level constraint. You can’t insert a duplicate row because the new_hire_id value can’t be the same in two or more rows.

You can duplicate all the columns in this constrained new_hire table except the new_hire_id column. The new_hire_id column acts as a primary key but is based on a surrogate key value. A surrogate key value is a stand-in value, like an actor that stands in for the primary actor who may be sick on any given night of a play. This is not a good design because any unique surrogate key should map to a unique natural key. A natural key is typically a set of columns that describe the unique object instance of the object type. The object type is the record or structure that defines the table. As a rule, natural key columns are typically mandatory columns or NOT NULL constrained.

In our little new_hire table, the natural key is composed by the first_name, last_name, and hire_date column values. They are all mandatory columns. Therefore, you should add a unique constraint to the new_hire table. You can do this by re-writing the CREATE TABLE statement or by using the ALTER TABLE statement.

You can modified your previous CREATE TABLE syntax by adding a table constraint to the table definition, like:

CREATE TABLE new_hire
( new_hire_id  NUMBER        CONSTRAINT new_hire_pk  PRIMARY KEY
, first_name   VARCHAR2(20)  CONSTRAINT new_hire_nn1 NOT NULL
, middle_name  VARCHAR2(20)
, last_name    VARCHAR2(20)  CONSTRAINT new_hire_nn2 NOT NULL
, hire_date    DATE          CONSTRAINT new_hire_nn3 NOT NULL
, CONSTRAINT   new_hire_nk   UNIQUE (first_name, middle_name, hire_date));

Alternatively, you would simply add to the definition of the existing new_hire table by using the ALTER TABLE statement. The syntax for the ALTER TABLE statement would be the following:

ALTER TABLE new_hire
ADD CONSTRAINT new_hire_nk UNIQUE (first_name, middle_name, hire_date);

As a rule, you seldom get the opportunity to redefine a table. You generally have to modify a table definition because it already contains data. Naturally, you may need to change the data to support any new business logic before you implement a new constraint.

The new_hire table is now constrained to ensure it has a valid primary key. The new_hire table also constrains the natural key with a UNIQUE constraint. Together, the primary key and unique constraint guarantee the fundamental integrity.

The column list for any INSERT statement must include all mandatory columns, like:

INSERT INTO new_hire
( new_hire_id
, first_name
, last_name
, hire_date )
VALUES
( new_hire_s.NEXTVAL
,'Henry'
,'Chabot'
,'31-JUL-1990' );

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 9999  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
     2 Henry      <Null>     Chabot     31-JUL-90

You should be able to see that all of the rows in the table contain meaningful data. This means you have designed a database tier that compels programmers that are writing the Application Programming Interface (API) to comply with your data governance rules.

Support Scripts

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

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