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