0693-0697

Previous Table of Contents Next

Page 693

CHAPTER 28

Enforcing Integrity

IN THIS CHAPTER

• A Common Integrity Problem694
• Column Constraints696
• Table Constraints702
• Using Sequences705
• Using Triggers707
• Application and Performance Considerations711

Page 694

Referential integrity is a condition that is present when all references to external objects within each database object are valid. Enforcing referential integrity is a critical task to ensuring that data is accurate and complete. Referential integrity problems can result in data loss, wasted storage, and inaccurate data. Overall database integrity is a broader issue; it relates to the values stored within a single object. Nonreferential problems with integrity typically are somewhat less serious and can result in inaccuracies and the storage of unwanted values.

Oracle provides many ways to enforce integrity, including column constraints, table constraints, sequences, and triggers. In this chapter, you will examine each of these methods by looking at some common examples.

A Common Integrity Problem

Many types of applications need to store information regarding individuals, companies, phone numbers , and addresses. This same information has been stored in databases in many ways, with varying degrees of success. Assume that, for the sake of this example, the following rules apply to the storage of this information:

• Individuals, companies, addresses, and phone numbers should not be stored redundantly.
• An individual may be related to zero, one, or many companies.
• Addresses and phone numbers may be related to an individual, a company, or an individual at a company.
• Addresses and phone numbers will have standard descriptions, such as "Home" and "Office."
• A phone number may or may not be related to an address.
• The current user and timestamp will be stored for each record when it is inserted or updated, and the application will use an optimistic locking scheme.

The DDL in Listing 28.1 provides an example of one possible physical model that will accommodate these rules.

Listing 28.1. This DDL contains the column attributes for the sample application that will be used to demonstrate integrity constraints.

 CREATE TABLE individual (      ID              NUMBER(10)     ,last_name       VARCHAR2(30)     ,first_name      VARCHAR2(30)     ,notes           VARCHAR2(255)     ,date_of_birth   DATE     ,last_updt_user  VARCHAR2(20)     ,last_updt_date  DATE ); CREATE TABLE company ( 

Page 695

 ID              NUMBER(10)     ,name            VARCHAR2(30)     ,notes           VARCHAR2(255)     ,last_updt_user  VARCHAR2(20)     ,last_updt_date  DATE ); CREATE TABLE ind_co_rel (      individual_id   NUMBER(10)     ,company_id      NUMBER(10)     ,title           VARCHAR2(80)     ,last_updt_user  VARCHAR2(20)     ,last_updt_date  DATE ); CREATE TABLE address (      ID              NUMBER(10)     ,address_type_id NUMBER(10)     ,address_line1   VARCHAR2(40)     ,address_line2   VARCHAR2(40)     ,address_line3   VARCHAR2(40)     ,city            VARCHAR2(40)     ,state           CHAR(2)     ,zip             NUMBER(5)     ,zip_4           NUMBER(4)     ,last_updt_user  VARCHAR2(20)     ,last_updt_date  DATE ); CREATE TABLE phone (      ID              NUMBER(10)     ,phone_type_id   NUMBER(10)     ,area_code       CHAR(3)     ,prefix          CHAR(3)     ,line            CHAR(4)     ,extension       VARCHAR2(6)     ,last_updt_user  VARCHAR2(20)     ,last_updt_date  DATE ); CREATE TABLE address_rel (      address_id      NUMBER(10)     ,individual_id   NUMBER(10)     ,company_id      NUMBER(10)     ,last_updt_user  VARCHAR2(20)     ,last_updt_date  DATE ); CREATE TABLE phone_rel (      phone_id        NUMBER(10)     ,address_id      NUMBER(10)     ,individual_id   NUMBER(10)     ,company_id      NUMBER(10)     ,last_updt_user  VARCHAR2(20)     ,last_updt_date  DATE ); 

 continues 

Page 696

Listing 28.1. continued

 CREATE TABLE address_type (      ID              NUMBER(10)     ,description     VARCHAR2(40)     ,last_updt_user  VARCHAR2(40)     ,last_updt_date  DATE ); CREATE TABLE phone_type (      ID              NUMBER(10)     ,description     VARCHAR2(40)     ,last_updt_user  VARCHAR2(40)     ,last_updt_date  DATE ); 

Although this data typically makes up only a small portion of a real application's data model, the many relationships among these tables present great potential for referential integrity problems. In the sections that follow, issues relating to the integrity of these objects illustrate the various means of enforcing integrity in Oracle databases.

Column Constraints

Column constraints are probably the most widely used means of enforcing integrity. Of these, PRIMARY KEY is the most significant. It is used to ensure that each row in the table is unique. When a column is declared as the PRIMARY KEY, an index on this column is created and assigned a unique name by Oracle. The additional constraints UNIQUE and NOT NULL are implied by the PRIMARY KEY constraint.

In the sample application, the ID column is used as the PRIMARY KEY in each table in which it occurs. This column contains unique numeric values generated by an Oracle sequence. Listing 28.2 contains the DDL for the individual table with the column constraint PRIMARY KEY enabled. Assume that the ID column is declared identically in each table in which it is used.

Listing 28.2. This DDL contains a PRIMARY KEY column constraint.

 CREATE TABLE individual (      ID              NUMBER(10)    PRIMARY KEY     ,last_name       VARCHAR2(30)     ,first_name      VARCHAR2(30)     ,notes           VARCHAR2(255)     ,date_of_birth   DATE     ,last_updt_user  VARCHAR2(20)     ,last_updt_date  DATE ); 

The generated column, ID, is necessary to create a primary key for the individual table, because no combination of columns is guaranteed to be unique. Although it is highly unlikely , two individuals with the same name and the same date of birth could exist in the database.

Page 697

A common mistake in developing database applications relates to lookup tables, which sometimes are created almost as an afterthought. The PRIMARY KEY constraint is particularly important to lookups because of the way in which they are used. If two rows of the lookup have the same value in the column that is used in joins, unwanted duplicates are returned in the result set. This point might seem obvious, but a simple oversight can cause this error to go unnoticed until long after a duplicate is inserted, when SQL statements joining to the lookup produce results that are visibly erroneous.

The UNIQUE constraint, which is implied by PRIMARY KEY, can be used on a different column to designate a secondary key. A UNIQUE constraint, similar to PRIMARY KEY, causes Oracle to create an index and assign it a unique name.

Listing 28.3 creates a unique index on the description column in the address_type lookup table. Although this method guarantees uniqueness for each row in the table, it still is preferable to use ID as the primary key. The primary key will be stored in another table, and because the description can be fairly long, using an ID (which will start at 1) conserves disk space. Another advantage of using the generated value as the primary key is that joins typically are faster on numeric values.

Listing 28.3. This DDL contains both a PRIMARY KEY and a UNIQUE constraint.

 CREATE TABLE address_type (      ID              NUMBER(10)   PRIMARY KEY     ,description     VARCHAR2(40) UNIQUE     ,last_updt_user  VARCHAR2(40)     ,last_updt_date  DATE ); 

TIP

You can use the CONSTRAINT keyword to assign a name to a constraint and its corresponding index. Listing 28.4 shows an example of the CONSTRAINT syntax for column constraints.

Listing 28.4. This DDL script demonstrates the use of the CONSTRAINT keyword.

 CREATE TABLE address_type (      ID              NUMBER(10)                      CONSTRAINT addr_type_pk   PRIMARY KEY     ,description     VARCHAR2(40)                      CONSTRAINT addr_type_desc UNIQUE     ,last_updt_id    VARCHAR2(40)     ,last_updt_date  DATE ); 

Previous Table of Contents Next