0713-0716

Previous Table of Contents Next

Page 713

Listing 28.22. These three functions guarantee that integrity constraints are satisfied for inserts into the objects in Listing 28.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) 

 continues 

Page 714

Listing 28.22. continued

 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 easily could 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 a company is not committed as part of the transaction that establishes the 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

Page 715

problem records. You can use a statement such as the following, for example, 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 designed properly, 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. Primary key constraints cause an index to be built, and primary key values often are 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 on 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 these methods and attempt to strike a balance between pure performance and the strict enforcement of integrity.

Page 716

Previous Table of Contents Next