PL/SQL Transactions   «Prev  Next»

Lesson 2Database integrity
ObjectiveDescribe how data integrity is compromised.

Database Integrity

Data integrity has two parts in a relational database:
  1. Valid relationships between tables
  2. Complete sets of interrelated data in tables

Valid Relationships between Tables

The first part of data integrity (valid relationships between tables) is enforced by foreign key constraints and requires no special handling in PL/SQL. The second part of data integrity (complete sets of interrelated data in tables) requires careful consideration when designing and coding PL/SQL. This is the issue described in this lesson. The concept of complete sets of data is best understood by looking at an example. To keep data integrity intact, you must be sure that complete sets of related data are added to the database and that the entire set of related data is removed if errors occur. Follow along with the following series of images to see how data integrity might be compromised in a telephone billing system.

Process Thousands of Records

In this imaginary telephone billing system, thousands of records are processed at the end of each billing cycle.
1) In this imaginary telephone billing system, thousands of records are processed at the end of each billing cycle.
The records are collected from many diverse sources and brought to the central billing system. Let us say that there is an invalid record in the middle of Jody Smith's records.

The central billing system sorts all the information by customer and feeds it all into a PL/SQL procedure
2) The central billing system sorts all the information by customer and feeds it all into a PL/SQL procedure

The procedure processes each record, adding the record to a history table and then updating the summary record for the summer's monthly billing statement
3) The procedure processes each record, adding the record to a history table and then updating the summary record for the summer's monthly billing statement

The invalid record causes the procedure to stop, leaving all the previous customer's records plus some portion of Jody Smith's records in the database.
4) The invalid record causes the procedure to stop, leaving all the previous customer's records plus some portion of Jody Smith's records in the database. At this point data integrity has been compromised.

Your best defense against this sort of data integrity problem is careful use of transaction controls in your procedure. Transaction controls are commands you can use inside PL/SQL to tell Oracle when one transaction starts and ends. Later lessons describe these commands. A transaction is a set of database activity that is handled as a unit by the database. If an error causes any part of the transaction to fail, Oracle automatically undoes the entire set of actions.

Enforcing Data Integrity.

Take advantage of Oracle’s transaction treatment to enforce data integrity. Use transaction controls in your procedure so that logical sets of actions are seen by Oracle as a single transaction. This way, any error found in the transaction causes the entire transaction to be undone and preserves data integrity. The next lesson describes how to implement transaction controls within PL/SQL blocks.