|Lesson 2||Database integrity |
|Objective||Describe how data integrity is compromised.|
Data integrity has two parts in a relational database:
- Valid relationships between tables
- 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.
1) Data Integrity1
2) Data Integrity2
3) Data Integrity3
4) Data Integrity4
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
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.