- 20

- Enforcing Integrity

- A Common Integrity Problem
- Column Constraints
- Table Constraints
- Using Sequences
- Using Triggers
- Application and Performance Considerations
- Summary

#
20

## Enforcing Integrity

Referential integrity is a condition in which all references to external objects within each database object are valid. Enforcing referential integrity is a critical task in 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, relating to the values stored within a single object. Non-referential problems with integrity are typically somewhat less serious, resulting in inaccuracies and the storage of unwanted values.

Oracle provides many ways to enforce integrity, including column constraints, table constraints, sequences, and triggers. Each of these methods will be examined in this chapter, through the use of a common example.

### A Common Integrity Problem

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

- Individuals, Companies, Addresses, and Phones should not be stored redundantly.
- An individual may be related to zero, one, or many companies.
- Addresses and Phones may be related to an individual, a company, or an individual at a company.
- Addresses and Phones will have standard descriptions, such as Home and Office.
- A phone 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 20.1 provides an example of one possible physical model that will accommodate these rules.

Listing 20.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 (

         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

    );

    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 an 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 are used to 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 automatically created and assigned a unique name by Oracle. The additional constraints UNIQUE and NOT NULL are implied by the PRIMARY KEY constraint.

In the example 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 20.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 where it is used.

Listing 20.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.

A common mistake in developing database applications relates to lookup tables, which are sometimes 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 will be returned in the result set. This point may 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, like PRIMARY KEY, causes Oracle to automatically create an index and assign it a unique name.

Listing 20.3 creates a unique index on the description column in the address_type lookup table. Although this guarantees uniqueness for each row in the table, it is still 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 to using the generated value as the primary key is that joins are typically faster on numeric values.

Listing 20.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

    );

The CONSTRAINT keyword can be used to assign a name to a constraint and its corresponding index. See Listing 20.4 for an example of the CONSTRAINT syntax for column constraints.

Listing 20.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

    );

The UNIQUE constraint does not prevent null values from being inserted. For this reason, it is often used for columns that should be unique but do not necessarily need to be populated. For example, an application may need to store the Social Security number for individuals if it is known, while allowing individuals to be inserted before their Social Security numbers are known. If a duplicate were to exist in this column, it would obviously be in error. However, the column cannot be defined as the primary key, because null values must be allowed.

The NOT NULL column constraint ensures that null values are not inserted into a column, and they can be used in conjunction with the UNIQUE constraint. Listing 20.5 demonstrates the use of NOT NULL as the sole constraint on a column, as well as its use in conjunction with a UNIQUE constraint.

Listing 20.5. This DDL script illustrates the uses of NOT NULL column constraints.

    CREATE TABLE address_type (

         ID              NUMBER(10)

                         CONSTRAINT addr_type_pk      PRIMARY KEY

        ,description     VARCHAR2(40)

                         CONSTRAINT addr_type_desc_u  UNIQUE

                         CONSTRAINT addr_type_desc_nn NOT NULL

        ,last_updt_id    VARCHAR2(40)

                         CONSTRAINT addr_type_lu_id   NOT NULL

        ,last_updt_date  DATE

                         CONSTRAINT addr_type_lu_dt   NOT NULL

    );

The NOT NULL constraint is used to simply define required columns, which may or may not be unique. For example, first and last name may be required to add an individual to the database. Specifying these columns with a NOT NULL constraint ensures that records missing either of these fields will not be inserted.

The most flexible column constraint is the CHECK constraint. A CHECK constraint can reference any column in the table, but it cannot reference any external objects, system variables, or system constants. When a CHECK constraint references columns, the conditions are always applied to the current row. In order to insert or update a column with the CHECK constraint, the specified condition must evaluate to TRUE or unknown (when a NULL value is being inserted into one of the columns referenced by the CHECK condition). CHECK constraints can be used in conjunction with other column constraints.

CHECK constraints, as the name implies, are particularly useful in sanity checking. Listing 20.6 demonstrates the use of a CHECK constraint on the individual table, which ensures that no individual can be inserted who is over 150 years old.

Listing 20.6. This DDL script features a CHECK constraint.

    CREATE TABLE individual (

         ID              NUMBER(10)

                         CONSTRAINT indiv_pk          PRIMARY KEY

        ,last_name       VARCHAR2(30)

                         CONSTRAINT indiv_l_name      NOT NULL

        ,first_name      VARCHAR2(30)

                         CONSTRAINT indiv_f_name      NOT NULL

        ,notes           VARCHAR2(255)

        ,date_of_birth   DATE

                         CONSTRAINT indiv_chk_bday

                         CHECK (date_of_birth > TO_DATE('01JAN1845',

                                'DDMONYYYY',

                                'nls_date_language = American'))

        ,last_updt_user  VARCHAR2(20)

                         CONSTRAINT indiv_lu_id NOT NULL

        ,last_updt_date  DATE

                         CONSTRAINT indiv_lu_dt NOT NULL

    );

When used as a column constraint, only one column can be referenced in the CHECK condition, and only one CHECK constraint is allowed per column.

To use the Social Security number as an example again, an application may want to store the dashes for easy reporting. You can use a CHECK constraint to ensure that the Social Security number is the correct length and that the dashes are in the correct positions.

The system catalog tables, user_indexes and user_constraints, contain all information regarding indexes and constraints. Listing 20.7 contains two simple SQL scripts that query these tables. The scripts can be executed in SQL*Plus or SQL*DBA to produce the output shown in Figure 20.1.

Listing 20.7. These simple SQL scripts query catalog tables to display information on existing indexes and constraints.

    /* INDEXES.SQL */

    select table_name, index_name, uniqueness

    from user_indexes

    order by 1, 2

    /

    /* CONST.SQL */

    select table_name, constraint_name, constraint_type

    from user_constraints

    order by 1, 2

    /

Figure 20.1. This output was generating by running the SQL scripts from Listing 20.7 in SQL*Plus.

Column constraints can be employed to enforce referential integrity for foreign keys using the REFERENCES keyword. The table and column specified in the REFERENCES clause must already exist, and the referenced column must be defined as a unique or PRIMARY KEY. Listing 20.9 demonstrates the use of two foreign keys in a table used only to join the referenced objects. Note that the script in Listing 20.8 must be run first, or the second REFERENCES constraint in Listing 20.9 will produce an error.

Listing 20.8. This DDL script creates the company table which is referenced by a foreign key constraint in Listing 20.9.

    CREATE TABLE company (

         ID              NUMBER(10)

                         CONSTRAINT company_pk PRIMARY KEY

        ,name            VARCHAR2(30)

                         CONSTRAINT co_name_u  UNIQUE

                         CONSTRAINT co_name_nn NOT NULL

        ,notes           VARCHAR2(255)

        ,last_updt_user  VARCHAR2(20)

                         CONSTRAINT co_lu_id   NOT NULL

        ,last_updt_date  DATE

                         CONSTRAINT co_lu_dt   NOT NULL

    );

Listing 20.9. This DDL script contains two REFERENCES column constraints.

    CREATE TABLE ind_co_rel (

         individual_id   NUMBER(10)

                         CONSTRAINT indco_ind_fk

                             REFERENCES individual(ID)

                             ON DELETE CASCADE

                         CONSTRAINT indco_ind_nn NOT NULL

        ,company_id      NUMBER(10)

                         CONSTRAINT indco_co_fk

                             REFERENCES company(ID)

                             ON DELETE CASCADE

                         CONSTRAINT indco_co_nn  NOT NULL

        ,title           VARCHAR2(80)

        ,last_updt_user  VARCHAR2(20)

                         CONSTRAINT indco_lu_id  NOT NULL

        ,last_updt_date  DATE

                         CONSTRAINT indco_lu_dt  NOT NULL

    );

The REFERENCES column constraint does not imply NOT NULL, but NOT NULL can be used in conjunction with a REFERENCES constraint on a column. As a result of the REFERENCES clauses in Listing 20.8, an application cannot insert a row into this table, unless both the individual_id and company_id values exist in their respective tables.

The data type is unnecessary for columns containing a REFERENCES constraint. The column will automatically be defined with the data type of the column it references.

The ON DELETE CASCADE option is a very significant feature of the REFERENCES column constraint. If it is not specified, rows with key values in the parent table cannot be deleted until all corresponding rows in the table containing the REFERENCES constraint are deleted. When ON DELETE CASCADE is specified, deletions in the parent table cause all references in the child table to be deleted automatically. In this respect, using ON DELETE CASCADE is similar to creating a DELETE trigger on the referenced table. This is an extremely powerful option and should be used with caution. If it is not properly understood, unwanted automatic deletions could result.

Although they are ideally suited to enforcing referential integrity, REFERENCES constraints may not be desirable in certain situations. For example, if a table has a high volume of transactions and contains several foreign keys that are simple lookups, performance can be adversely affected by using the REFERENCES constraint. Each time a row is inserted or updated, the referenced tables must be checked to ensure that each foreign key being inserted exists in the referenced tables. Depending on the nature of the data and the importance of performance, it may be preferable to enforce the foreign keys through the application itself.

### Table Constraints

Each of the column constraints described in the previous section can also be applied as table constraints, with the exception of NOT NULL. Table constraints have the additional advantage of being able to operate on multiple columns.

Refer to the CHECK constraint in Listing 20.6. This provides a sanity check on the lower bounds of date_of_birth, but it would be better to check the upper bounds as well. Listing 20.10 illustrates how you can accomplish this using a table constraint.

Listing 20.10. This DDL script, containing a CHECK table constraint, is an improvement on the column constraint used in Listing 20.6.

    CREATE TABLE individual (

         ID              NUMBER(10)

                         CONSTRAINT indiv_pk          PRIMARY KEY

        ,last_name       VARCHAR2(30)

                         CONSTRAINT indiv_l_name      NOT NULL

        ,first_name      VARCHAR2(30)

                         CONSTRAINT indiv_f_name      NOT NULL

        ,notes           VARCHAR2(255)

        ,date_of_birth   DATE

        ,last_updt_user  VARCHAR2(20)

                         CONSTRAINT indiv_lu_id NOT NULL

        ,last_updt_date  DATE

                         CONSTRAINT indiv_lu_dt NOT NULL

        ,CONSTRAINT indiv_chk_bday

             CHECK (date_of_birth BETWEEN (TO_DATE('01JAN1845',

                    'DDMONYYYY', 'nls_date_language = American'))

                    AND last_updt_date)

    );

The syntax of table constraints is nearly identical to that of column constraints, except that the CONSTRAINT clause comes at the end of the column list.

Another advantage to using a table constraint applies only to constraints that result in the creation of an index. The table constraint syntax allows the designation of a tablespace and storage specification for the corresponding index. When indexes are created automatically for column constraints, they are created in the default tablespace using the default storage specification. The script in Listing 20.11 adds a UNIQUE constraint to the ind_co_rel table, enforcing uniqueness in the combination of individual_id and company_id. It also implements the USING INDEX clause to designate a tablespace and storage specification for the index that will be created.

Listing 20.11. This DDL script uses the ALTER TABLE syntax to create a UNIQUE table constraint and a storage specification for the corresponding index.

    ALTER TABLE ind_co_rel

    ADD CONSTRAINT ind_co_rel_u UNIQUE(individual_id, company_id)

        USING INDEX

        TABLESPACE oracle_unleashed

        STORAGE (INITIAL     10K

                 NEXT        10K

                 MAXEXTENTS  100

                 PCTINCREASE  10);

The syntax for tablespace and storage specification following the USING INDEX keyword is identical to that for the CREATE INDEX statement. The USING INDEX clause can also be used in a PRIMARY KEY constraint, as illustrated in Listing 20.12.

Listing 20.12. This DDL script removes the UNIQUE constraint created in Listing 20.11 and re-creates it as a PRIMARY KEY.

    ALTER TABLE ind_co_rel

    DROP CONSTRAINT ind_co_rel_u

    /

    ALTER TABLE ind_co_rel

    DROP CONSTRAINT indco_ind_nn

    /

    ALTER TABLE ind_co_rel

    DROP CONSTRAINT indco_co_nn

    /

    ALTER TABLE ind_co_rel

    ADD CONSTRAINT ind_co_rel_pk PRIMARY KEY(individual_id, company_id)

        USING INDEX

        TABLESPACE oracle_unleashed

        STORAGE (INITIAL     10K

                 NEXT        10K

                 MAXEXTENTS  100

                 PCTINCREASE  10)

    /

Notice that the NOT NULL constraints on the individual_id and company_id columns were also dropped. To keep them would be redundant, because columns included in a PRIMARY KEY constraint cannot have null values.

Foreign key table constraints are implemented in much the same way as REFERENCING column constraints. If necessary, multiple columns can be included in a FOREIGN KEY table constraint. This is not the case with the column constraint implementation. Note also that the FOREIGN KEY keyword is only available for the table constraint syntax. Listing 20.13, the final script for ind_co_rel, also defines temporary tables into which exceptions are logged.

Listing 20.13. The final ind_co_rel script uses the FOREIGN KEY table constraint syntax and logs exceptions into temporary tables.

    CREATE TABLE ind_co_rel (

         individual_id   NUMBER(10)

        ,company_id      NUMBER(10)

        ,title           VARCHAR2(80)

        ,last_updt_user  VARCHAR2(20)

                         CONSTRAINT indco_lu_id  NOT NULL

                         EXCEPTIONS INTO ind_co_err_1

        ,last_updt_date  DATE

                         CONSTRAINT indco_lu_dt  NOT NULL

                         EXCEPTIONS INTO ind_co_err_2

        ,CONSTRAINT indco_ind_fk FOREIGN KEY (individual_id)

              REFERENCES individual(ID)

              ON DELETE CASCADE

              EXCEPTIONS INTO ind_co_err_3

        ,CONSTRAINT indco_co_fk  FOREIGN KEY (company_id)

              REFERENCES company(ID)

              ON DELETE CASCADE

              EXCEPTIONS INTO ind_co_err_4

        ,CONSTRAINT ind_co_rel_pk PRIMARY KEY(individual_id, company_id)

              USING INDEX

              TABLESPACE oracle_unleashed

              STORAGE (INITIAL     10K

                       NEXT        10K

                       MAXEXTENTS  100

                       PCTINCREASE  10)

              EXCEPTIONS INTO ind_co_err_5

    )

    TABLESPACE oracle_unleashed;

Each type of exception is logged into a different temporary table in Listing 20.13, which is helpful in debugging and resolving integrity problems. The tables into which exceptions are logged should be identical to the ind_co_rel table, except that they should have no constraints, for obvious reasons. The EXCEPTIONS INTO clause can be used for any type of column or table constraint. If the table referenced by EXCEPTIONS INTO does not exist, using the clause will produce an error.

The EXCEPTIONS INTO clause is particularly useful in applications where frequent and timely bulk loads are required. In order to use SQL*Loader with the direct path option to maximize its performance, constraints must be disabled. When the constraints are re-enabled, the EXCEPTIONS INTO clause can be used to categorize problem records by inserting them in separate tables based on the constraint that was violated. This minimizes the negative impact constraints have on performance, while maintaining integrity and providing a means of identifying and resolving problems with the data being loaded.

As shown by these examples, Oracle provides a wide variety of options for enforcing integrity through column and table constraints. Constraints are powerful tools for enforcing integrity, but they should be used with care. Overuse of constraints can add significantly to long-term maintenance requirements, and misuse can create unwanted dependencies or unnecessary exceptions. The possible trade-offs involving constraints and performance will be discussed in greater detail later in this chapter.

### Using Sequences

Oracle sequences are ideally suited to the task of generating unique key values. A sequence is a stored object that simply generates a sequence of numbers. Listing 20.14 illustrates the syntax for creating a sequence.

Listing 20.14. This sequence is used to generate primary keys for the individual table.

    CREATE SEQUENCE individual_ids

        START WITH 1

        INCREMENT BY 1

        NOMAXVALUE;

The values specified for the START WITH, and INCREMENT BY parameters in Listing 20.14 are the defaults. NOMAXVALUE is the default as well. The script in Listing 20.13 will produce the same result as the following:

    CREATE SEQUENCE individual_ids;

It is a good practice to explicitly declare these defaults for documentation purposes, if nothing else. The implementation is fairly self-explanatory. The START WITH parameter indicates the first number that will be generated, INCREMENT BY specifies a number to be added to the current value to generate the next value, and NOMAXVALUE indicates that there is no maximum to the numbers it will generate, (practically no maximum, although there is an upper limit).

The MINVALUE parameter is used only by descending sequences. (Specifying a negative value for INCREMENT BY produces a descending sequence.) CYCLE, when used in conjunction with MAXVALUE or MINVALUE, indicates that the sequence should start from the beginning when the minimum or maximum value is reached. The default for this option is NOCYCLE.

The CACHE parameter indicates the number of values that should be pre-generated and cached by the sequence. The default value for CACHE is 20. Raising this parameter can improve performance in high transaction volume environments.

ORDER ensures that sequences are used in the order generated. Regardless of the setting of this option, the same value will never be returned twice. If an application uses a sequence in a transaction that is rolled back, the value is simply discarded. NORDER, the default for this option, is acceptable unless the sequence is being used like a timestamp to indicate the order of events over time.

A sequence has two pseudocolumns, currval and nextval. Currval returns the current value of the sequence, while nextval increments the sequence and returns the new value. Listing 20.15 demonstrates how a sequence can be used in an Oracle function to generate new keys when inserting records.

Listing 20.15. This function accesses a sequence to fetch a new key when inserting a record.

    CREATE OR REPLACE FUNCTION insert_indiv (last CHAR, first CHAR,

                               notes CHAR, dob DATE) RETURN NUMBER

    IS

        new_id NUMBER;

    BEGIN

        SELECT individual_ids.nextval INTO new_id FROM dual;

        INSERT INTO individual VALUES (new_id, last, first, notes,

                                       dob, user, sysdate);

        COMMIT;

        RETURN(new_id);

    EXCEPTION

        WHEN OTHERS THEN

            ROLLBACK;

            RETURN(1);

    END insert_indiv;

It is a common practice to use sequences for generating unique primary keys. One sequence can be used for many tables, or a separate sequence can be created for each table requiring generated keys. Either option is preferable to any key that requires user intervention, because typographical errors are bound to occur. It is typically preferable to generate unique keys even when one exists naturally in the data (Social Security number, for example).

Using a sequence to generate primary keys can improve performance in certain situations, as well. As mentioned previously, integer joins are typically faster than character-based joins, and even when a natural integer primary key exists, a sequence is often a better choice. To use Social Security number as an example, 10 bytes must be stored for each key. If a sequence is used, starting with 1, a considerable amount of disk space can be conserved, and a much smaller index produced, which will result in less I/O. Perhaps a less important consideration is the order in which values are inserted. Depending on how inserts are handled by applications accessing the sequence, the index on a sequence-generated primary key may be created in ascending order naturally, which is somewhat of an optimization in terms of I/O performance. If the sequence is created with the ORDER option, and inserts are handled using Oracle subprograms similar to Listing 20.15, this will always be true.

### Using Triggers

Triggers are stored procedures associated with a specific operation on a specific table. A trigger is automatically fired when the operation with which it is associated is performed on the table with which it is associated. Triggers can perform many of the same tasks as constraints, and in most cases, they can go beyond what constraints can do. For example, a NOT NULL constraint can only ensure that a value is present in a column, but it does nothing to ensure the accuracy of the data. Listing 20.16 provides an example of how you can use triggers to enforce a NOT NULL constraint and ensure the accuracy of the data being inserted.

Listing 20.16. This trigger ensures that the columns last_updt_user and last_updt_date are inserted and updated accurately.

    CREATE OR REPLACE TRIGGER indiv_timestamp

    BEFORE INSERT OR UPDATE ON individual FOR EACH ROW

    BEGIN

        :new.last_updt_user := user;

        :new.last_updt_date := sysdate;

    END indiv_timestamp;

The simple trigger in Listing 20.16 ensures that the last_updt_user and last_updt_date are being inserted and updated accurately in the individual table. In effect, the trigger intercepts the actual values being inserted and replaces them with user and sysdate. Using the NOT NULL constraint for these columns is no longer necessary, and the trigger goes far beyond what the constraint could do.

The trigger in Listing 20.16 also relieves the application of the burden of supplying values for the last_updt_user and last_updt_date columns when inserting and updating records.

INSERT and UPDATE triggers are commonly used for customized transaction logging, or to generate statistical summaries to be accessed by a different group of users than those applying the transactions. For example, a large order entry system might use an INSERT trigger to write only the date, the order amount, and the salesperson to a separate table to be used only for management reporting.

The syntax for creating triggers is very similar to the creation of procedures, with a few notable exceptions. The BEFORE or AFTER keyword must follow the name of the trigger, indicating whether it should be fired before or after the operation that causes it is fired.

Although it may not seem logical to do so, this trigger had to be created with the BEFORE option. Trying to implement this trigger with the AFTER option produces the following error:
ORA04091: table SCOTTY.INDIVIDUAL is mutating, trigger/function may not see it
Because the trigger is being executed by a process that is currently involved in a transaction on the same row, it cannot be created with the AFTER option. This would, in effect, invalidate the old correlation of the trigger.

Immediately following the BEFORE or AFTER keyword is the action (or actions) with which the trigger is associated. This can be INSERT, UPDATE, or DELETE, or any combination of these separated by OR. The FOR EACH ROW keyword defines the behavior of the trigger when it is fired by statements affecting multiple rows. The default behavior is to fire the trigger only once, regardless of the number of rows affected. A trigger may also include a WHEN clause, which limits the conditions under which it will fire.

The WHEN clause can be used for specialized reporting, or to draw attention to a value that may seem to be out of range. For example, an accounts payable system might use an INSERT trigger to log all payments of greater than $10,000 to a temporary table, which can then be used to generate a report for management's review and approval. This could be an alternative to a CHECK condition, which might prove to be overly restrictive. In most circumstances, it would not be acceptable to reject a valid payment simply because it is unusually high. On the other hand, management may be interested in reviewing or auditing these payments. In this respect, a trigger can be used in a way that is analogous to passively enforcing a CHECK constraint.

Note that in Listing 20.16, the variable new is never declared. This is the default correlation name associated with the new row (which is valid for inserts and updates only). The name old is associated with the old row by default, and is valid for updates and deletes only. These default names can be reassigned using a REFERENCING clause. The REFERENCING clause should placed immediately before the FOR EACH ROW keyword (if it is used), as in Listing 20.17.

Listing 20.17. This trigger uses a REFERENCING clause to rename new.

    CREATE OR REPLACE TRIGGER indiv_timestamp

    BEFORE INSERT OR UPDATE ON individual

    REFERENCING new AS new_row FOR EACH ROW

    BEGIN

        :new_row.last_updt_user := user;

        :new_row.last_updt_date := sysdate;

    END indiv_timestamp;

The REFERENCING clause is rarely used, but it is provided in order to allow the use of new and old as regular program variables.

Triggers, when used with sequences, can also be used to enforce primary key constraints. Listing 20.18 creates a trigger that ensures that new rows in the individual table are assigned unique primary key values. The trigger also ensures that the sequence is used, by overriding any value specified in an insert statement with the next value in the sequence.

Listing 20.18. This trigger enforces the primary key on the individual table.

    CREATE OR REPLACE TRIGGER indiv_get_key

    BEFORE INSERT ON individual FOR EACH ROW

    DECLARE

        new_id NUMBER;

    BEGIN

        SELECT individual_ids.nextval INTO new_id FROM dual;

        :new.id := new_id;

    END indiv_get_key;

Unfortunately, this trigger cannot coexist with the trigger in Listing 20.17. For each table, only one trigger can exist for each operation. Listing 20.19 demonstrates how these can be implemented together, replacing the CHECK constraint on date_of_birth in the process.

Listing 20.19. These triggers can be used to enforce several constraints on the individual table.

    CREATE OR REPLACE TRIGGER indiv_ins_trg

    BEFORE INSERT ON individual FOR EACH ROW

    DECLARE

        new_id NUMBER;

    BEGIN

        IF ((:new.date_of_birth < TO_DATE('01JAN1845', 'DDMONYYYY',

             'nls_date_language = American'))

    OR (:new.date_of_birth > sysdate)) THEN

            RAISE_APPLICATION_ERROR(20001, 'Invalid birth date.');

        ELSE

            SELECT individual_ids.nextval INTO new_id FROM dual;

            :new.id := new_id;

            :new.last_updt_user := user;

            :new.last_updt_date := sysdate;

        END IF;

    END indiv_ins_trig;

    CREATE OR REPLACE TRIGGER indiv_updt_trg

    BEFORE UPDATE ON individual FOR EACH ROW

    BEGIN

        IF ((:new.date_of_birth < TO_DATE('01JAN1845',

             'DDMONYYYY', 'nls_date_language = American'))

            OR (:new.date_of_birth > sysdate)) THEN

            RAISE_APPLICATION_ERROR(20001, 'Invalid birth date.');

        ELSE

            :new.id := :old.id;

            :new.last_updt_user := user;

            :new.last_updt_date := sysdate;

        END IF;

    END indiv_updt_trg;

In Listing 20.19, the PRIMARY KEY constraint is enforced for both inserts and updates. The UPDATE trigger completely prevents an ID from being changed, which might not be acceptable if this were not a generated key. The triggers also enforce a CHECK constraint on date_of_birth and the NOT NULL constraints on last_updt_user and last_updt_date.

Triggers can be used to enforce foreign key constraints, as well. Refer to Listing 20.9. The REFERENCES constraints in the example both contained the ON DELETE CASCADE option. If the foreign keys integrity constraints are enforced by the triggers in Listing 20.20, the REFERENCES column constraints could be removed.

Listing 20.20. These triggers can be used to enforce foreign key constraints.

    CREATE OR REPLACE TRIGGER indiv_del_trg

    BEFORE DELETE ON individual FOR EACH ROW

    BEGIN

        DELETE FROM ind_co_rel WHERE individual_id = :old.id;

    END indiv_del_trg;

    CREATE OR REPLACE TRIGGER co_del_trg

    BEFORE DELETE ON company FOR EACH ROW

    BEGIN

        DELETE FROM ind_co_rel WHERE company_id = :old.id;

    END co_del_trg;

    CREATE OR REPLACE TRIGGER ind_co_trg

    BEFORE INSERT OR UPDATE ON ind_co_rel FOR EACH ROW

    DECLARE

        co_id    NUMBER;

        indiv_id NUMBER;

    BEGIN

        SELECT ID INTO co_id FROM company WHERE ID = :new.company_id;

        SELECT ID INTO indiv_id FROM individual

                  WHERE ID = :new.individual_id;

        EXCEPTION

            WHEN OTHERS THEN

                 RAISE_APPLICATION_ERROR(20002, 'Invalid id.');

    END ind_co_trg;

As these examples demonstrate, triggers can be used to perform the same tasks as table and column constraints. In many cases, it may be preferable to use triggers because they are likely to provide better performance. This is particularly true in distributed environments, where it may not be possible to enforce foreign key constraints at all.

When designing triggers, you should pay special attention to cascading triggers. Cascading occurs when a trigger on one table causes a trigger on another table to be fired. Codependencies, in particular, can be a problem.

Note that in Listing 20.20, the individual and company tables both have DELETE triggers that delete corresponding rows from ind_co_rel. For the sake of example, assume that ind_co_rel has a DELETE trigger that deletes corresponding rows in address_rel and phone_rel, and that individual and company also include these deletions in their DELETE triggers.

This presents numerous integrity problems. If a relationship between an individual and a company is deleted, records in address_rel and phone_rel that are related to both should be deleted. Also, if an individual or company is deleted entirely, all address_rel and phone_rel records related to the specific individual or company should be deleted. When an individual or company is deleted, the ind_co_rel record is deleted, which causes its trigger to be fired, resulting in deletions from the address_rel and phone_rel tables. If these records are also to be deleted by the trigger that was originally fired by the deletion of the individual or company, the mutating table problem described earlier will occur. In this case, the ind_co_rel should probably not have a DELETE trigger at all. Meaningless records in address_rel and phone_rel exist only until the corresponding individual or company is deleted. This is just one example of how cascading triggers can produce unexpected results.

### Application and Performance Considerations

Regardless of how integrity is enforced in the database, the application must have knowledge of the constraints. The application must be able to submit transaction statements in the proper order, and it must know how to respond to exceptions resulting from integrity problems. This point is best illustrated through the use of an example. Assume that the application needs to perform a single transaction based on the objects created in Listing 20.21.

Listing 20.21. These objects participate in a single transaction.

    CREATE SEQUENCE individual_ids

        START WITH 1

        INCREMENT BY 1

        NOMAXVALUE;

    CREATE TABLE individual (

         ID              NUMBER(10)

                         CONSTRAINT indiv_pk          PRIMARY KEY

        ,last_name       VARCHAR2(30)

                         CONSTRAINT indiv_l_name      NOT NULL

        ,first_name      VARCHAR2(30)

                         CONSTRAINT indiv_f_name      NOT NULL

        ,notes           VARCHAR2(255)

        ,date_of_birth   DATE

        ,last_updt_user  VARCHAR2(20)

                         CONSTRAINT indiv_lu_id NOT NULL

        ,last_updt_date  DATE

                         CONSTRAINT indiv_lu_dt NOT NULL

        ,CONSTRAINT indiv_chk_bday

             CHECK (date_of_birth BETWEEN (TO_DATE('01JAN1845',

                    'DDMONYYYY', 'nls_date_language = American'))

                    AND last_updt_date)

    );

    CREATE SEQUENCE company_ids

        START WITH 1

        INCREMENT BY 1

        NOMAXVALUE;

    CREATE TABLE company (

         ID              NUMBER(10)

                         CONSTRAINT company_pk PRIMARY KEY

        ,name            VARCHAR2(30)

                         CONSTRAINT co_name_u  UNIQUE

                         CONSTRAINT co_name_nn NOT NULL

        ,notes           VARCHAR2(255)

        ,last_updt_user  VARCHAR2(20)

                         CONSTRAINT co_lu_id   NOT NULL

        ,last_updt_date  DATE

                         CONSTRAINT co_lu_dt   NOT NULL

    );

    CREATE TABLE ind_co_rel (

         individual_id   NUMBER(10)

        ,company_id      NUMBER(10)

        ,title           VARCHAR2(80)

        ,last_updt_user  VARCHAR2(20)

                         CONSTRAINT indco_lu_id  NOT NULL

                         EXCEPTIONS INTO ind_co_err_1

        ,last_updt_date  DATE

                         CONSTRAINT indco_lu_dt  NOT NULL

                         EXCEPTIONS INTO ind_co_err_2

        ,CONSTRAINT indco_ind_fk FOREIGN KEY (individual_id)

              REFERENCES individual(ID)

              ON DELETE CASCADE

              EXCEPTIONS INTO ind_co_err_3

        ,CONSTRAINT indco_co_fk  FOREIGN KEY (company_id)

              REFERENCES company(ID)

              ON DELETE CASCADE

              EXCEPTIONS INTO ind_co_err_4

        ,CONSTRAINT ind_co_rel_pk PRIMARY KEY(individual_id, company_id)

              USING INDEX

              TABLESPACE oracle_unleashed

              STORAGE (INITIAL     10K

                       NEXT        10K

                       MAXEXTENTS  100

                       PCTINCREASE  10)

              EXCEPTIONS INTO ind_co_err_5

    );

It would be useful if the application could insert an individual, a company, and a record relating the two in one transaction. The foreign key constraints on indiv_co_rel dictate that this record must be inserted last. In designing a process to complete this transaction, you should also consider that the application will need to insert an individual and a company separately, as well. Listing 20.22 provides three functions to accomplish these tasks.

Listing 20.22. These three functions guarantee that integrity constraints are satisfied for INSERTS into the objects in Listing 20.21.

    CREATE OR REPLACE FUNCTION insert_indiv (last CHAR, first CHAR,

                               notes CHAR, dob DATE)

    RETURN NUMBER

    IS

        invalid_name EXCEPTION;

        new_id       NUMBER;

    BEGIN

        IF ((LENGTH(RTRIM(last)) > 0) AND

            (LENGTH(RTRIM(first)) > 0)) THEN

            SELECT individual_ids.nextval INTO new_id FROM dual;

            INSERT INTO individual VALUES (new_id, last, first,

                        notes, dob, user, sysdate);

            RETURN(new_id);

        ELSE

            RAISE invalid_name;

        END IF;

    EXCEPTION

        WHEN invalid_name THEN

            ROLLBACK;

            RETURN(20001);

        WHEN OTHERS THEN

            ROLLBACK;

            RETURN(1);

    END insert_indiv;

    CREATE OR REPLACE FUNCTION insert_company (name CHAR, notes CHAR)

    RETURN NUMBER

    IS

        invalid_name EXCEPTION;

        new_id       NUMBER;

    BEGIN

        IF (LENGTH(RTRIM(name)) > 0) THEN

            SELECT company_ids.nextval INTO new_id FROM dual;

            INSERT INTO company VALUES (new_id, name, notes, user,

                                        sysdate);

            RETURN(new_id);

        ELSE

            RAISE invalid_name;

        END IF;

    EXCEPTION

        WHEN invalid_name THEN

            ROLLBACK;

            RETURN(20001);

        WHEN OTHERS THEN

            ROLLBACK;

            RETURN(1);

    END insert_company;

    CREATE OR REPLACE FUNCTION insert_ind_co(last CHAR, first CHAR,

                               notes CHAR, dob DATE, co_name CHAR,

                               co_notes CHAR, title CHAR)

    RETURN NUMBER

    IS

        ret          NUMBER;

        ind_id       NUMBER;

        co_id        NUMBER;

    BEGIN

        ret:=insert_indiv(last, first, notes, dob);

        IF (ret > 0) THEN

            ind_id:=ret;

            ret:=insert_company(co_name, co_notes);

            IF (ret > 0) THEN

                co_id:=ret;

                INSERT INTO ind_co_rel VALUES (ind_id, co_id, title,

                            user, sysdate);

                RETURN(ind_id);

            ELSE

                ROLLBACK;

                RETURN(ret);

            END IF;

        ELSE

            ROLLBACK;

            RETURN(ret);

        END IF;

    EXCEPTION

        WHEN OTHERS THEN

            ROLLBACK;

            RETURN(1);

    END insert_ind_co;

The transaction ensures that all constraints are being met, with the exception of the check constraint on individual, which could easily be added to the insert_indiv function. Sequences are used to generate primary keys, the functions check for null values, and the foreign key constraints in ind_co_rel are enforced by the order in which inserts occur. If an error occurs in any of the transactions, a rollback is issued, but commits are left out altogether. This is to ensure that an individual or company is not committed as part of the transaction that establishes their relationship. It is up to the calling application to commit the transaction as a whole.

These functions illustrate the point that in some cases, it may be a good solution to allow the client application to enforce integrity. Column and table constraints can be costly, especially foreign key constraints, which require an additional read. In an environment with a high volume of transactions and users, these constraints can have a significant negative impact on performance, possibly resulting in unnecessary contention, snapshot problems, and other bottlenecks.

When using the application itself to enforce referential integrity, you can run batch processes periodically to ensure that the application is enforcing integrity. You can create temporary tables to store exceptions for further analysis and resolution, and you can use simple SQL to identify problem records. For example, you can use a statement like the one following to identify invalid references to individuals in the ind_co_rel table.

    SELECT * FROM ind_co_rel INTO temp_no_ind WHERE individual_id NOT IN

             (SELECT ID FROM individual)

If the application is properly designed, batch processes should not identify exceptions and would need to be run very infrequently.

Primary key constraints, on the other hand, typically improve performance, especially if they are defined as a single small column. This constraint causes an index to be built, and primary key values are often stored as foreign keys in other tables. In these cases, declaring the primary key usually results in faster joins.

In general, however, constraints have a negative impact on overall performance. Depending on the nature of the data, security considerations, and standards of acceptable performance, you can rely upon the application itself to enforce many of the required integrity constraints.

### Summary

Column constraints, table constraints, triggers, and PL/SQL are all useful in enforcing integrity, but each approach also has limitations. The best way to combine these elements to enforce integrity depends entirely on the application. Most designs use a combination of all of these methods and attempt to strike a balance between pure performance and the strict enforcement of integrity.