Page 693
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.
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:
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 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 );