025-030

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