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 Slide Show to see how data integrity might be compromised in a telephone billing system.

Data Integrity Telephone Billing
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.