Previous Table of Contents Next


Listing 2.2 A revised table creation script using constraints.

CREATE TABLE STUDENTS AS
(ssn                       NOT NULL   number(9)      primary key,
 first_name                NOT NULL   varchar2 (10),
 last_name                 NOT NULL   varchar2 (12),
 middle_name                          varchar2 (10),
 street_address            NOT NULL   varchar2 (30),
 apartment_number                     varchar2 (4),
 city                      NOT NULL   varchar2 (30),
 state_code                NOT NULL   varchar2 (2),
 zip_code                  NOT NULL   number (5),
 home_phone                NOT NULL   number (10),
 degree_plan                          varchar2 (20),
 overall_gpa                          number (3, 2),
 most_recent_gpa                      number (3, 2),
 financing_num             NOT NULL   integer (9) unique,
 foreign key (degree_plan) references DEGREE_PLANS.degree_plan);

This revised table enforces the following conditions:

  The columns ssn, first_name, last_name, street_address, city, state_code, zip_code, home_phone, and financing_num must all have a value in every row contained in the table.
  The primary key of the table is the ssn column. This ensures that each student has a unique social security number. The definition of the primary key constraint also creates an index on the table.
  The value of the degree_plan column must exist in the degree_plan column of the table DEGREE_PLANS. A student must be following an existing degree program.

As an application developer with PL/SQL, you will rarely be required to create tables using constraints, but a working knowledge of constraints and their limitations will make you a better developer.

What Is Referential Integrity?

Simply put, referential integrity occurs when a value in one table must agree with a value in another table. In our revised STUDENTS table, a condition of referential integrity exists between the STUDENTS table and the DEGREE_PLANS table, because the student’s degree plan must correspond to a predefined degree plan.

Referential integrity occurs in three types:

  One-to-one
  One-to-many
  Many-to-many

Understanding each type of referential integrity is crucial to being successful as a PL/SQL application developer.

One-To-One Relationships

A one-to-one relationship occurs when a single row in one table corresponds to one (and only one) row in another table. In our revised STUDENTS table, notice that each student has been given a unique financing_num value; this value will be used to provide a one-to-one relationship with the STUDENT_FINANCIAL_AID table. Because this value is unique, a reference to the STUDENT_FINANCIAL_AID table can be made once the proper financing_num value is queried from the STUDENTS table. Figure 2.1 illustrates this one-to-one relationship.


Figure 2.1  A one-to-one relationship.

One-To-Many Relationships

A one-to-many relationship occurs when a single row of one table corresponds to multiple rows in another table. In our revised STUDENTS table, the column ssn will be referenced by the ssn column in the ENROLLED_CLASSES table. Each student can be enrolled in one or several classes, so multiple rows in ENROLLED_CLASSES will contain the ssn for one student. Figure 2.2 illustrates this one-to-many relationship.


Figure 2.2  A one-to-many relationship.

Many-To-Many Relationships

A many-to-many relationship occurs when one or more rows of a table correspond to one or more rows in another table. For instance, the CLASSES table contains a list of classes; each class has a unique class_number value. One or more students can each take the same class. A student can even take the same class multiple times (consecutively, one would hope). Therefore, the CLASSES table and the STUDENTS table have a many-to-many relationship (via the ENROLLED_CLASSES table). Figure 2.3 illustrates this many-to-many relationship.


Figure 2.3  A many-to-many relationship.

Tables with many-to-many relationships usually (but not always) have a table that acts as an intersection for the two tables. In this case, the ENROLLED_CLASSES table fills that role; notice that each of these tables has a one-to-many relationship with the ENROLLED_CLASSES table.


Previous Table of Contents Next