Oracle7 Server Concepts

Definition of Data Integrity

It is important that data adhere to a predefined set of rules, as determined by the database administrator or application developer. As an example of data integrity, consider the tables EMP and DEPT and the business rules for the information in each of the tables, as illustrated in Figure 7 - 1.

Figure 7 - 1. Examples of Data Integrity

Note that certain columns of each table have specific rules that constrain the data contained within them.

Types of Data Integrity

The following types of rules are applied to tables and enable you to enforce different types of data integrity.

Nulls

A rule defined on a single column that allows or disallows inserts or updates of rows containing a null for the column.

Unique Column Values

A rule defined on a column (or set of columns) that allows only the insert or update of a row containing a unique value for the column (or set of columns).

Primary Key Values

A rule defined on a column (or set of columns) so that each row in the table can be uniquely identified by the values in the column (or set of columns).

Referential Integrity

A rule defined on a column (or set of columns) in one table that allows the insert or update of a row only if the value for the column or set of columns (the dependent value) matches a value in a column of a related table (the referenced value).

Referential integrity also includes the rules that dictate what types of data manipulation are allowed on referenced values and how these actions affect dependent values. The rules associated with referential integrity include:

Restrict A referential integrity rule that disallows the update or deletion of referenced data.
Set to Null When referenced data is updated or deleted, all associated dependent data is set to NULL.
Set to Default When referenced data is updated or deleted, all associated dependent data is set to a default value.
Cascade When referenced data is updated, all associated dependent data is correspondingly updated; when a referenced row is deleted, all associated dependent rows are deleted.

Complex Integrity Checking

A user-defined rule for a column (or set of columns) that allows or disallows inserts, updates, or deletes of a row based on the value it contains for the column (or set of columns).

How Oracle Enforces Data Integrity

Oracle allows you to define and enforce each type of the data integrity rules defined in the previous section. Most of these rules are easily defined using integrity constraints.

Integrity Constraints

An integrity constraint is a declarative method of defining a rule for a column of a table. Oracle supports the following integrity constraints:

NOT NULL integrity constraints for the rules associated with nulls in a column

UNIQUE key integrity constraints for the rule associated with unique column values

PRIMARY KEY integrity constraints for the rule associated with primary identification values

FOREIGN KEY integrity constraints for the rules associated with referential integrity. Oracle currently supports the use of FOREIGN KEY integrity constraints to define the referential integrity actions, including

update and delete RESTRICT

delete CASCADE

CHECK integrity constraints for complex integrity rules

Other referential integrity actions not included on this list can be defined using database triggers (see the following section).

Note: You cannot enforce referential integrity using declarative integrity constraints if child and parent tables are on different nodes of a distributed database. However, you can enforce referential integrity in a distributed database using database triggers (see next section).

Database Triggers

Oracle also allows you to enforce integrity rules with a non-declarative approach using database triggers (stored database procedures automatically invoked on insert, update, or delete operations). While database triggers allow you to define and enforce any type of integrity rule, it is strongly recommended that you use database triggers only in the following situations:

to enforce referential integrity when a required referential integrity rule cannot be enforced using the integrity constraints listed above: update CASCADE, update and delete SET NULL, update and delete SET DEFAULT

to enforce referential integrity when child and parent tables are on different nodes of a distributed database

to enforce complex business rules not definable using integrity constraints

For more information and examples of database triggers used to enforce data integrity, see Chapter 15, "Database Triggers".

Restrict	A referential integrity rule that disallows the update or deletion of referenced data.
Set to Null	When referenced data is updated or deleted, all associated dependent data is set to NULL.
Set to Default	When referenced data is updated or deleted, all associated dependent data is set to a default value.
Cascade	When referenced data is updated, all associated dependent data is correspondingly updated; when a referenced row is deleted, all associated dependent rows are deleted.