Data Integrity
Does one's integrity ever lie in what he is not able to do?
Flannery O'Connor: Wise Blood
This chapter explains how to enforce the business rules associated with your database and prevent the entry of invalid information into tables using integrity constraints. The chapter includes:
If you are using Trusted Oracle, see the Trusted Oracle7 Server Administrator's Guide for more information about integrity constraints in that environment.
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
- 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 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".
An Introduction to Integrity Constraints
Oracle uses integrity constraints to prevent invalid data entry into the base tables of the database. You can define integrity constraints to enforce the business rules that are associated with the information in a database. If any of the results of a DML statement execution violate an integrity constraint, Oracle rolls back the statement and returns an error.
Note: Operations on views (and synonyms for tables) are subject to the integrity constraints defined on the underlying base tables.
For example, assume that you define an integrity constraint for the SAL column of the EMP table. This integrity constraint enforces the rule that no row in this table can contain a numeric value greater than 10,000 in this column. If an INSERT or UPDATE statement attempts to violate this integrity constraint, Oracle rolls back the statement and returns an informative error.
The integrity constraints implemented in Oracle fully comply with the standards set forth by ANSI X3.135-1989 and ISO 9075-1989.
Advantages of Integrity Constraints
Integrity constraints are not the only way to enforce data integrity rules on the data of your database. You can also
- enforce business rules using triggered stored database procedures (see Chapter 15, "Database Triggers")
The following section describes some of the advantages that integrity constraints have over these other alternatives.
Declarative Ease
Because you define integrity constraints using SQL commands, when you define or alter a table, no programming is required. Therefore, they are easy to write, eliminate programming errors, and Oracle controls their functionality. For these reasons, declarative integrity constraints are preferable to application code and database triggers. The declarative approach is also better than using stored procedures because, unlike the stored procedure solution to data integrity by controlled data access, integrity constraints do not eliminate the flexibility of ad hoc data access.
Centralized Rules
Integrity constraints are defined for tables (not an application) and stored in the data dictionary. Therefore, the data entered by any application must adhere to the same integrity constraints associated with a table. By moving business rules from application code to centralized integrity constraints, the tables of a database are guaranteed to contain valid data, no matter which database application manipulates the information. Stored procedures cannot provide the same advantage of centralized rules stored with a table, and although database triggers can provide this benefit, the complexity of implementation is far greater than the declarative approach used for integrity constraints.
Maximum Application Development Productivity
If a business rule enforced by an integrity constraint changes, the administrator need only change that integrity constraint and all applications automatically adhere to the modified constraint. Alternatively, if a business rule is enforced by the code of each database application, developers must modify all application source code and recompile, debug, and test the modified applications.
Immediate User Feedback
Because Oracle stores specific information about each integrity constraint in the data dictionary, you can design database applications to use this information to provide immediate user feedback about integrity constraint violations, even before Oracle executes and checks the SQL statement. For example, a SQL*Forms application can use integrity constraint definitions stored in the data dictionary to check for violations as values are entered into the fields of a form, even before the application issues a statement.
Superior Performance
Because the semantics of integrity constraint declarations are clearly defined, performance optimizations are implemented for each specific declarative rule. The Oracle query optimizer can use declarations to learn more about data to improve overall query performance. (Also, taking integrity rules out of application code and database triggers guarantees that checks are only done when necessary.)
Flexibility for Data Loads and Identification of Integrity Violations
Integrity constraints can be temporarily disabled so that large amounts of data can be loaded without the overhead of constraint checking. When the data load is complete, you can easily enable the integrity constraints, and you can automatically report any new rows that violate integrity constraints to a separate exceptions table.
The Performance Cost of Integrity Constraints
The advantages of enforcing data integrity rules do not come without some loss in performance. In general, the "cost" of including an integrity constraint is, at most, the same as executing a SQL statement that evaluates the constraint.
Types of Integrity Constraints
The integrity constraints that you can use to impose restrictions on the input of column values can be of the following types:
The following sections explain each type of integrity constraint in detail. The information in each section includes the following:
- the rule enforced by the constraint
- an example of the constraint
- recommendations for appropriate use of the constraint
- other important information about the constraint
NOT NULL Integrity Constraints
By default, all columns in a table allow nulls (the absence of a value). A NOT NULL constraint requires that no nulls be allowed in a column of a table. For example, you can define a NOT NULL constraint to require that a value be input in the ENAME column for every row of the EMP table.
Figure 7 - 2 illustrates a NOT NULL integrity constraint.
Figure 7 - 2. NOT NULL Integrity Constraints
UNIQUE Key Integrity Constraints
A UNIQUE key integrity constraint requires that no two rows of a table have duplicate values in a specified column or set of columns.
For example, consider the DEPT table in Figure 7 - 3. A UNIQUE key constraint is defined on the DNAME column to disallow rows with duplicate department names.
Figure 7 - 3. A UNIQUE Key Constraint
Unique Keys
The column (or set of columns) included in the definition of the UNIQUE key constraint is called the unique key. The term "unique key" is often incorrectly used as a synonym for the terms "UNIQUE key constraint" or "UNIQUE index"; however, note that the term "key" refers only to the list of columns used in the definition of the integrity constraint.
If the UNIQUE key constraint is comprised of more than one column, that group of columns is said to be a composite unique key. For example, in Figure 7 - 4, the CUSTOMER table has a UNIQUE key constraint defined on the composite unique key: the AREA and PHONE columns.
Figure 7 - 4. A Composite UNIQUE Key Constraint
This UNIQUE key constraint allows you to enter an area code and phone number any number of times, but the combination of a given area code and given phone number cannot be duplicated in the table. This eliminates unintentional duplication of a phone number.
UNIQUE Key Constraints and Indexes
Oracle enforces unique integrity constraints with indexes. In Figure 7 - 4, Oracle enforces the UNIQUE key constraint by implicitly creating a unique index on the composite unique key. Because Oracle enforces UNIQUE key constraints using indexes, composite UNIQUE key constraints are limited to the same limitations imposed on composite indexes: up to 16 columns can constitute a composite unique key, and the total size (in bytes) of a key value cannot exceed approximately half the associated database's block size.
Combining UNIQUE Key and NOT NULL Integrity Constraints
Notice in the examples of the previous section that UNIQUE key constraints allow the input of nulls unless you also define NOT NULL constraints for the same columns. In fact, any number of rows can include nulls for columns without NOT NULL constraints because nulls are not considered equal. A null in a column (or in all columns of a composite UNIQUE key) always satisfies a UNIQUE key constraint.
It is common to define unique keys on columns with NOT NULL integrity constraints. This combination forces the user to input values in the unique key; this combination of data integrity rules eliminates the possibility that any new row's data will ever risk conflicting with an existing row's data.
Note: Because of the search mechanism for UNIQUE constraints on more than one column, you cannot have identical values in the non-null columns of a partially null composite UNIQUE key constraint.
PRIMARY KEY Integrity Constraints
Each table in the database can have at most one PRIMARY KEY constraint. The values in the group of one or more columns subject to this constraint constitute the unique identifier of the row. In effect, each row is named by its primary key values.
The Oracle implementation of the PRIMARY KEY integrity constraint guarantees that both of the following are true:
- No two rows of a table have duplicate values in the specified column or set of columns.
- The primary key columns do not allow nulls (that is, a value must exist for the primary key columns in each row).
Primary Keys
The column (or set of columns) included in the definition of a table's PRIMARY KEY integrity constraint is called the primary key. Although it is not required, every table should have a primary key so that
- each row in the table can be uniquely identified
- no duplicate rows exist in the table
Figure 7 - 5 illustrates a PRIMARY KEY constraint in the DEPT table and examples of rows that the constraint prevents from entering the table.
Figure 7 - 5. A Primary Key Constraint
PRIMARY KEY Constraints and Indexes
Oracle enforces all PRIMARY KEY constraints using indexes. In the previous example, the primary key constraint created for the DEPTNO column is enforced by
- the implicit creation of a unique index on that column
Because Oracle enforces primary key constraints using indexes, composite primary key constraints are limited to 16 columns, which is the same limitation imposed on composite indexes. The name of the index is the same as the name of the constraint. Also, you can specify the storage options for the index by including the ENABLE clause in the CREATE TABLE or ALTER TABLE statement used to create the constraint.
Referential Integrity and FOREIGN KEY (Referential) Integrity Constraints
Because tables of a relational database can be related by common columns, the rules that govern the relationship of the columns must be maintained. Referential integrity rules guarantee that these relationships are preserved.
There are several terms associated with referential integrity constraints:
Foreign Key The column or set of columns included in the definition of the referential integrity constraint that reference a referenced key (see the following).
Referenced Key
The unique key or primary key of the same or different table that is referenced by a foreign key.
Dependent or Child Table
A dependent or child table is the table that includes the foreign key. Therefore, it is the table that is dependent on the values present in the referenced unique or primary key.
Referenced or Parent Table
A referenced or parent table is the table that is referenced by the child table's foreign key. It is this table's referenced key that determines whether specific inserts or updates are allowed in the child table.
A referential integrity constraint requires that for each row of a table, the value in the foreign key matches a value in a parent key.
Figure 7 - 6 illustrates the above terms.
Figure 7 - 6 shows a foreign key defined on the DEPTNO column of the EMP table. It guarantees that every value in this column must match a value in the primary key of the DEPT table (the DEPTNO column). Therefore, no erroneous department numbers can exist in the DEPTNO column of the EMP table.
Foreign keys can be comprised of multiple columns. However, a composite foreign key must reference a composite primary or unique key with the same number of columns and datatypes. Because composite primary and unique keys are limited to 16 columns, a composite foreign key is also limited to 16 columns.
Figure 7 - 6. Referential Integrity Constraints
Self-Referential Integrity Constraints
Another type of referential integrity constraint, shown in Figure 7 - 7, is called a self-referential integrity constraint. This type of foreign key references a parent key of the same table. In the example below, you define the referential integrity constraint so that every value in the MGR column of the EMP table corresponds to a value that currently exists in the EMPNO column of the same table (that is, every manager must also be an employee). This integrity constraint eliminates the possibility of erroneous employee numbers in the MGR column.
Figure 7 - 7. Single Table Referential Constraints
Nulls and Foreign Keys
The relational model permits foreign keys to be a value of the referenced primary or unique key, or null. There are several possible interpretations of this basic rule of the relational model when composite (multicolumn) foreign keys are involved.
The ANSI/ISO SQL92 (entry-level) standard permits a composite foreign key to contain any value in its non-null columns if any other column is null, even if those non-null values are not found in the referenced key. By using other constraints (for example, NOT NULL and CHECK constraints), you can alter the treatment of partially null foreign keys from this default treatment.
A composite foreign key can be all null, all non-null, or partially null. The following terms define three alternative matching rules for composite foreign keys:
match full Partially null foreign keys are not permitted. Either all components of the foreign key must be null, or the combination of values contained in the foreign key must appear as the primary or unique key value of a single row of the referenced table.
match partial
Partially null composite foreign keys are permitted. Either all components of the foreign key must be null, or the combination of non-null values contained in the foreign key must appear in the corresponding portion of the primary or unique key value of a single row in the referenced table.
match none
Partially null composite foreign keys are permitted. If any column of a composite foreign key is null, then the non-null portions of the key do not have to match any corresponding portion of a parent key.
Actions Defined by Referential Integrity Constraints
Referential integrity constraints also specify particular actions that are performed on the dependent rows in a child table if a referenced parent key value is modified. The referential actions supported by the FOREIGN KEY integrity constraints of Oracle include UPDATE and DELETE RESTRICT, and DELETE CASCADE.
Note: Other referential actions not supported by FOREIGN KEY integrity constraints of Oracle can be enforced using database triggers. See Chapter 15, "Database Triggers," for more information regarding database triggers.
Update and Delete Restrict The restrict action specifies that referenced key values cannot be updated or deleted if the resulting data would violate a referential integrity constraint. For example, if a primary key value is referenced by a value in the foreign key, the referenced primary key value cannot be deleted because of the dependent data.
Delete Cascade The delete cascade action specifies that when rows containing referenced key values are deleted, all rows in child tables with dependent foreign key values are also deleted. Therefore, the delete cascades. For example, if a row in a parent table is deleted, and this row's primary key value is referenced by one or more foreign key values in a child table, the rows in the child table that reference the primary key value are also deleted from the child table.
DML Restrictions with Respect to Referential Actions Table 7 - 1 outlines the DML statements allowed by the different referential actions on the primary/unique key values in the parent table, and the foreign key values in the child table.
| DML Statement
| Issued Against Parent Table
| Issued Against Child Table
|
| INSERT
| Always OK if parent key value is unique.
| OK only if the foreign key value exists in the parent key or is partially or all null.
|
| UPDATE Restrict
| Allowed if the statement does not leave any rows in the child table without a referenced parent key value.
| Allowed if the new foreign key value still references a referenced key value.
|
| DELETE Restrict
| Allowed if no rows in the child table reference the parent key value.
| Always OK.
|
| DELETE Cascade
| Always OK.
| Always OK.
|
Table 7 - 1. DML Statements Allowed by Update and Delete Restrict
CHECK Integrity Constraints
A CHECK integrity constraint on a column or set of columns requires that a specified condition be true or unknown for every row of the table. If a DML statement is issued so that the condition of the CHECK constraint evaluates to false, the statement is rolled back.
The Check Condition
CHECK constraints allow you to enforce very specific or sophisticated integrity rules with the specification of a check condition. The condition of a CHECK constraint has some limitations, including that the condition must be a Boolean expression evaluated using the values in the row being inserted or updated, and cannot contain subqueries, sequences, the SYSDATE, UID, USER, or USERENV SQL functions, or the pseudocolumns LEVEL or ROWNUM.
In evaluating CHECK constraints that contain string literals or SQL functions with NLS parameters as arguments (such as TO_CHAR, TO_DATE, and TO_NUMBER), Oracle uses the database's NLS settings by default. You can override the defaults by specifying NLS parameters explicitly in such functions within the CHECK constraint definition. (For more information on NLS features, see Oracle7 Server Reference.)
Multiple CHECK Constraints
A single column can have multiple CHECK constraints that reference the column in its definition. There is no limit to the number of CHECK constraints that you can define on a column.
The Mechanisms of Constraint Checking
To know what types of actions are permitted when constraints are present, it is useful to understand when Oracle actually performs the checking of constraints. To illustrate this, an example or two is helpful. Assume the following:
- The EMP table has been defined as illustrated in a previous example (see Figure 7 - 7
![[*]](jump.gif)
.).
- The self-referential constraint makes the entries in the MGR column dependent on the values of the EMPNO column. For simplicity, the rest of this discussion only addresses the EMPNO and MGR columns of the EMP table.
Consider the insertion of the first row into the EMP table. No rows currently exist, so how can a row be entered if the value in the MGR column cannot reference any existing value in the EMPNO column? The three possibilities include the following:
- A null can be entered for the MGR column of the first row, assuming that the MGR column does not have a NOT NULL constraint defined on it.
- The same value can be entered in both the EMPNO and MGR columns.
- A multiple row INSERT statement, such as an INSERT statement with nested SELECT statement, can insert rows that reference one another. For example, the first row might have EMPNO as 200 and MGR as 300, while the second row might have EMPNO as 300 and MGR as 200.
Each case reveals something about how and when Oracle performs constraint checking.
The first case is easy to understand; a null is given for the foreign key value. Because nulls are allowed in foreign keys, this row is inserted successfully into the table.
The second case is more interesting. This case reveals when Oracle effectively performs its constraint checking: after the statement has been completely executed. To allow a row to be entered with the same values in the parent key and the foreign key, Oracle must first execute the statement (that is, insert the new row) and then check to see if any row in the table has an EMPNO that corresponds to the new row's MGR.
The third case reveals even more about the constraint checking mechanism. This scenario shows that constraint checking is effectively deferred until the complete execution of the statement; all rows are inserted first, then all rows are checked for constraint violations.
As another example of this third case, consider the same self-referential integrity constraint and the following scenario:
- The company has been sold. Because of this sale, all employee numbers must be updated to be the current value plus 5000 to coordinate with the new company's employee numbers. Because manager numbers are really employee numbers, these values must also increase by 5000.
The table currently exists as illustrated in Figure 7 - 8.
Figure 7 - 8. The EMP Table Before Updates
UPDATE emp
SET empno = empno + 5000,
mgr = mgr + 5000;
Even though a constraint is defined to verify that each MGR value matches an EMPNO value, this statement is legal because Oracle effectively performs its constraint checking after the statement completes. Figure 7 - 9 shows that Oracle performs the actions of the entire SQL statement before any constraints are checked.
Figure 7 - 9. Constraint Checking
The examples in this section illustrated the constraint checking mechanism during INSERT and UPDATE statements. The same mechanism is used for all types of DML statements, including UPDATE, INSERT, and DELETE statements.
The examples also used self-referential integrity constraints to illustrate the checking mechanism. However, the same mechanism is used for all types of constraints, including NOT NULL, UNIQUE key, PRIMARY KEY, all types of FOREIGN KEY, and CHECK constraints.
Default Column Values and Integrity Constraint Checking
Default values are included as part of an INSERT statement before the statement is parsed. Therefore, default column values are subject to all integrity constraint checking.
