Skip Headers
Oracle® Database Concepts
11g Release 1 (11.1)

B28318-06
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

21 Data Integrity

This chapter explains how to use integrity constraints to enforce the business rules associated with your database and prevent the entry of invalid information into tables.

This chapter contains the following topics:

Introduction to Data Integrity

It is important that column data adhere to a predefined set of rules, as determined by the database administrator or application developer.

For example, some columns in a database table can have specific rules that constrain the data contained within them. These constraints can affect how data columns in one table relate to those in another table.

This section includes the following topics:

Data Integrity Rules

This section describes the rules that can be applied to table columns to enforce different types of data integrity.

Null rule: A null rule is a rule defined on a single column that allows or disallows inserts or updates of rows containing a null (the absence of a value) in that column.

Unique column values: A unique value rule defined on a column (or set of columns) allows the insert or update of a row only if it contains a unique value in that column (or set of columns).

Primary key values: A primary key value rule defined on a key (a column or set of columns) specifies that each row in the table can be uniquely identified by the values in the key.

Referential integrity rules: A referential integrity rule is a rule defined on a key (a column or set of columns) in one table that guarantees that the values in that key match the values in a key in 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 are:

  • Restrict: 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.

  • No action: Disallows the update or deletion of referenced data. This differs from RESTRICT in that it is checked at the end of the statement, or at the end of the transaction if the constraint is deferred. (Oracle Database uses No Action as its default action.)

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 Database Enforces Data Integrity

Oracle Database enables you to define and enforce each type of data integrity rule defined in the previous section. Most of these rules are easily defined using either integrity constraints or database triggers (stored database procedures automatically invoked on insert, update, or delete operations).

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 Also:

Chapter 22, "Triggers" for examples of triggers used to enforce data integrity

Constraint States

  • ENABLE ensures that all incoming data conforms to the constraint

  • DISABLE allows incoming data, regardless of whether it conforms to the constraint

  • VALIDATE ensures that existing data conforms to the constraint

  • NOVALIDATE means that some existing data may not conform to the constraint

In addition:

  • ENABLE VALIDATE is the same as ENABLE. The constraint is checked and is guaranteed to hold for all rows.

  • ENABLE NOVALIDATE means that the constraint is checked, but it does not have to be true for all rows. This allows existing rows to violate the constraint, while ensuring that all new or modified rows are valid.

    In an ALTER TABLE statement, ENABLE NOVALIDATE resumes constraint checking on disabled constraints without first validating all data in the table.

  • DISABLE NOVALIDATE is the same as DISABLE. The constraint is not checked and is not necessarily true.

  • DISABLE VALIDATE disables the constraint, drops the index on the constraint, and disallows any modification of the constrained columns.

    For a UNIQUE constraint, the DISABLE VALIDATE state enables you to load data efficiently from a nonpartitioned table into a partitioned table using the EXCHANGE PARTITION clause of the ALTER TABLE statement.

Transitions between these states are governed by the following rules:

  • ENABLE implies VALIDATE, unless NOVALIDATE is specified.

  • DISABLE implies NOVALIDATE, unless VALIDATE is specified.

  • VALIDATE and NOVALIDATE do not have any default implications for the ENABLE and DISABLE states.

  • When a unique or primary key moves from the DISABLE state to the ENABLE state, if there is no existing index, a unique index is automatically created. Similarly, when a unique or primary key moves from ENABLE to DISABLE and it is enabled with a unique index, the unique index is dropped.

  • When any constraint is moved from the NOVALIDATE state to the VALIDATE state, all data must be checked (this can be very slow). However, moving from VALIDATE to NOVALIDATE simply forgets that the data was ever checked.

  • Moving a single constraint from the ENABLE NOVALIDATE state to the ENABLE VALIDATE state does not block reads, writes, or other DDL statements. It can be done in parallel.

See Also:

Overview of Integrity Constraints

Oracle Database 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 you want to associate with the information in a database. If any of the results of a DML statement execution violate an integrity constraint, Oracle Database 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 salary column of the employees table that enforces a 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 Database rolls back the statement and returns an information error message.

This section includes the following topics:

Advantages of Integrity Constraints

This section describes some of the advantages that integrity constraints associated with database tables have over other alternatives. These advantages are:

  • Enforcing business rules in the code of a database application

  • Using stored procedures to completely control access to data

  • Enforcing business rules with triggered stored database procedures

This section includes the following topics:

Declarative Ease

Declarative integrity constraints are preferable to application code and database triggers. Because you define integrity constraints using SQL statements, no additional programming is required when you define or alter a table. The SQL statements are easy to write and eliminate programming errors. Oracle Database controls their functionality.

The declarative approach is also better than using stored procedures, because the stored procedure solution to data integrity controls data access, but integrity constraints do not eliminate the flexibility of random data access.

The semantics of integrity constraint declarations are clearly defined, and performance optimizations are implemented for each specific declarative rule. The Oracle Database 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 made when necessary.)

Centralized Rules

Integrity constraints are defined for tables (not applications) and are stored in the data dictionary. Therefore, any data entered by any application must adhere to the same integrity constraints associated with the table. By keeping business rules in application code centralized integrity constraints rather than in application code, the tables of a database are guaranteed to contain valid data, no matter which database application manipulates the information.

Maximum Application Development Productivity

If a business rule enforced by an integrity constraint changes, then the administrator need only change that integrity constraint and all applications automatically adhere to the modified constraint. In contrast, if the business rule were enforced by the code of each database application, developers would have to modify all application source code and recompile, debug, and test the modified applications.

Immediate User Feedback

Oracle Database 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 Database runs and checks the SQL statement. For example, an Oracle 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.

Flexibility for Data Loads and Identification of Integrity Violations

You can disable integrity constraints temporarily 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 come with 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

You can use integrity constraints to impose restrictions on the input of values in both normal and virtual columns. You can use the following constraints:

See Also:

"Overview of Tables" for a conceptual description of virtual columns, and Oracle Database SQL Language Reference for reference information about virtual columns

NOT NULL Integrity Constraints

By default, all columns in a table allow nulls. Null means the absence of a value. A NOT NULL constraint requires a column of a table contain no null values.

For certain types of tables and column datatypes, when adding a column that has both a NOT NULL constraint and a default value, the database can optimize the operation and reduce the amount of time that the table is locked for DML. The database stores metadata in the table that describes the default value in the added column. As a result, the database does not need to populate every row with the default value when you add the column, thereby minimizing the time that the table is locked.

You can only add a column with a NOT NULL constraint if the table does not contain any rows or if you specify a default value.

See Also:

UNIQUE Key Integrity Constraints

A UNIQUE key integrity constraint requires that every value in a column or set of columns (key) be unique—that is, no two rows of a table have duplicate values in a specified column or set of columns.

This section includes the following topics:

Unique Keys

The columns included in the definition of the UNIQUE key constraint are called the unique key. If the unique key consists of more than one column, then that group of columns is called a composite unique key.

Unique key is often incorrectly used as a synonym for the term UNIQUE key constraint or UNIQUE index. However, key refers only to the column or set of columns used in the definition of the integrity constraint.

For example, the UNIQUE key constraint might let you enter an area code and telephone number any number of times, but the combination of a given area code and given telephone number cannot be duplicated in the table. This eliminates unintentional duplication of a telephone number.

Combining UNIQUE Key and NOT NULL Integrity Constraints

Columns with both unique keys and NOT NULL integrity constraints are common. This combination forces the user to enter values in the unique key and also eliminates the possibility that any new row's data will ever conflict 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 Database 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.

This section includes the following topics:

Primary Keys

The columns included in the definition of a table's PRIMARY KEY integrity constraint are 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

PRIMARY KEY Constraints and Indexes

Oracle Database enforces all PRIMARY KEY constraints using indexes. The primary key constraint created for a column is enforced by the implicit creation of:

  • A unique index on that column

  • A NOT NULL constraint for that column

Composite primary key constraints are limited to 32 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. If a usable index exists when a primary key constraint is created, then the primary key constraint uses that index rather than implicitly creating a new one.

Referential Integrity Constraints

Different tables in a relational database can be related by common columns, and the rules that govern the relationship of the columns must be maintained. Referential integrity rules guarantee that these relationships are preserved.

Table 21-1 lists terms associated with referential integrity constraints.

Table 21-1 Referential Integrity Constraint Terms

Term Definition

Foreign key

The column or set of columns included in the definition of the referential integrity constraint that reference a referenced key.

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

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

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 21-1 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 (also the deptno column). Therefore, no erroneous department numbers can exist in the deptno column of the emp table.

Foreign keys can be defined as multiple columns. However, a composite foreign key must reference a composite primary or unique key with the same number of columns and the same datatypes. Because composite primary and unique keys are limited to 32 columns, a composite foreign key is also limited to 32 columns.

Figure 21-1 Referential Integrity Constraints

Description of Figure 21-1 follows
Description of "Figure 21-1 Referential Integrity Constraints"

This section includes the following topics:

Self-Referential Integrity Constraints

Another type of referential integrity constraint, shown in Figure 21-2, is called a self-referential integrity constraint. This type of foreign key references a parent key in the same table.

In Figure 21-2, the referential integrity constraint ensures 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, but not necessarily in the same row, because every manager must also be an employee. This integrity constraint eliminates the possibility of erroneous employee numbers in the mgr column.

Figure 21-2 Single Table Referential Constraints

Description of Figure 21-2 follows
Description of "Figure 21-2 Single Table Referential Constraints"

Nulls and Foreign Keys

The relational model permits the value of foreign keys to match either the referenced primary or unique key value, or be null. 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 can specify particular actions to be 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 Database are UPDATE and DELETE NO ACTION, and DELETE CASCADE.

Note:

Other referential actions not supported by FOREIGN KEY integrity constraints of Oracle Database can be enforced using database triggers.

See Chapter 22, "Triggers" for more information.

This section includes the following topics:

DELETE NO ACTION

The No Action (default) option 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, then the referenced primary key value cannot be deleted because of the dependent data.

DELETE CASCADE

A delete cascades when rows containing referenced key values are deleted, causing all rows in child tables with dependent foreign key values to also be deleted. 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, then the rows in the child table that reference the primary key value are also deleted from the child table.

DELETE SET NULL

A delete sets null when rows containing referenced key values are deleted, causing all rows in child tables with dependent foreign key values to set those values to null. For example, if employee_id references manager_id in the TMP table, then deleting a manager causes the rows for all employees working for that manager to have their manager_id value set to null.

DML Restrictions with Respect to Referential Actions

Table 21-2 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.

Table 21-2 DML Statements Allowed by Update and Delete No Action

DML Statement Issued Against Parent Table Issued Against Child Table

INSERT

Always OK if the parent key value is unique.

OK only if the foreign key value exists in the parent key or is partially or all null.

UPDATE NO ACTION

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 NO ACTION

Allowed if no rows in the child table reference the parent key value.

Always OK.

DELETE CASCADE

Always OK.

Always OK.

DELETE SET NULL

Always OK.

Always OK.


Concurrency Control, Indexes, and Foreign Keys

Oracle Database maximizes the concurrency control of parent keys in relation to dependent foreign keys. Locking behavior depends on whether foreign key columns are indexed. If foreign keys are not indexed, then the child table will probably be locked more frequently, deadlocks will occur, and concurrency will be decreased. For this reason foreign keys should almost always be indexed. The only exception is when the matching unique or primary key is never updated or deleted

This section includes the following topics:

No Index on the Foreign Key

In the following circumstances, the database acquires a table lock on the child table:

  • No index exists on the foreign key column of the child table.

    For example, assume that hr.departments table is a parent of hr.employees, which contains the unindexed foreign key department_id.

  • A session modifies a primary key in the parent table (for example, deletes a row or modifies primary key attributes) or merges data into the parent table. Inserts into the parent table do not acquire table locks on the child table.

    For example, a database session deletes row 3 from the departments table, as shown in Figure 21-3.

Figure 21-3 Locking Mechanisms with Unindexed Foreign Key

Description of Figure 21-3 follows
Description of "Figure 21-3 Locking Mechanisms with Unindexed Foreign Key"

In Figure 21-3, an unindexed foreign key column in the child table causes the deletion of row 3 in the parent to acquire a share table lock on the child table. This lock enables other transactions to query but not update the table. For example, phone numbers in employees cannot be updated while the departments row is being deleted. The table lock releases immediately after the DML on the departments table completes. If multiple rows are affected, then the lock is obtained and released once for each row.

Note:

DML on a child table does not acquire a table lock on the parent table.
Index on the Foreign Key

If a foreign key column in the child table is indexed, then DML on the parent table acquires a table lock on the parent table. This lock prevents transactions from acquiring exclusive table locks, but does not prevent DML on the parent or the child table while the DML on the parent table occurs. This situation is preferable if updates or deletions occur on the parent table while updates occur on the child table.

Figure 21-4 shows a scenario in which the foreign key column in the child table is indexed. The parent table is departments and the child table is employees. A session updates row 3 in departments. The DML on departments does not prevent updates to employees, although updates and deletions of rows in departments must wait for row-level locks on the indexes of employees to clear.

Figure 21-4 Locking Mechanisms with Indexed Foreign Key

Description of Figure 21-4 follows
Description of "Figure 21-4 Locking Mechanisms with Indexed Foreign Key"

If the child table specifies ON DELETE CASCADE, then deletions from the parent table can result in deletions from the child table. For example, a deletion of a record from departments can cause the deletion of records from employees for employees in the deleted department. In this case, waiting and locking rules are the same as if you deleted rows from the child table after deleting rows from the parent table.

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 table row. If a DML statement results in the condition of the CHECK constraint evaluating to false, then the statement is rolled back.

This section includes the following topics:

The Check Condition

CHECK constraints let you enforce very specific integrity rules by specifying a check condition. The condition of a CHECK constraint has some limitations:

  • It must be a Boolean expression evaluated using the values in the row being inserted or updated, and

  • It cannot contain subqueries; sequences; the SQL functions SYSDATE, UID, USER, or USERENV; or the pseudocolumns LEVEL or ROWNUM.

In evaluating CHECK constraints that contain string literals or SQL functions with globalization support parameters as arguments (such as TO_CHAR, TO_DATE, and TO_NUMBER), Oracle Database uses the database globalization support settings by default. You can override the defaults by specifying globalization support parameters explicitly in such functions within the CHECK constraint definition.

See Also:

Oracle Database Globalization Support Guide for more information on globalization support features

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.

If you create multiple CHECK constraints for a column, design them carefully so their purposes do not conflict. Do not assume any particular order of evaluation of the conditions. Oracle Database does not verify that CHECK conditions are not mutually exclusive.

The Mechanisms of Constraint Checking

To know what types of actions are permitted when constraints are present, it is useful to understand when Oracle Database actually performs the checking of constraints. Assume the following:

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? Three possibilities for doing this are:

Consider the same self-referential integrity constraint in this 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 (see Figure 21-5).

Figure 21-5 The EMP Table Before Updates

Description of Figure 21-5 follows
Description of "Figure 21-5 The EMP Table Before Updates"

UPDATE employees 
  SET employee_id = employee_id + 5000, 
      manager_id = manager_id + 5000; 

Even though a constraint is defined to verify that each mgr value matches an empno value, this statement is legal because Oracle Database effectively performs its constraint checking after the statement completes. Figure 21-6 shows that Oracle Database performs the actions of the entire SQL statement before any constraints are checked.

Figure 21-6 Constraint Checking

Description of Figure 21-6 follows
Description of "Figure 21-6 Constraint Checking"

The examples in this section illustrate 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. The same mechanism is used for all types of constraints, including the following:

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.

Deferred Constraint Checking

You can defer checking constraints for validity until the end of the transaction.

If a constraint causes an action (for example, delete cascade), that action is always taken as part of the statement that caused it, whether the constraint is deferred or immediate.

This section includes the following topics:

Constraint Attributes

You can define constraints as either deferrable or not deferrable, and either initially deferred or initially immediate. These attributes can be different for each constraint. You specify them with keywords in the CONSTRAINT clause:

  • DEFERRABLE or NOT DEFERRABLE

  • INITIALLY DEFERRED or INITIALLY IMMEDIATE

Constraints can be added, dropped, enabled, disabled, or validated. You can also modify a constraint's attributes.

See Also:

Oracle Database SQL Language Reference for information about constraint attributes and their default values

SET CONSTRAINTS Mode

The SET CONSTRAINTS statement makes constraints either DEFERRED or IMMEDIATE for a particular transaction (following the ANSI SQL92 standards in both syntax and semantics). You can use this statement to set the mode for a list of constraint names or for ALL constraints.

The SET CONSTRAINTS mode lasts for the duration of the transaction or until another SET CONSTRAINTS\ statement resets the mode.

SET CONSTRAINTS ... IMMEDIATE causes the specified constraints to be checked immediately on execution of each constrained statement. Oracle Database first checks any constraints that were deferred earlier in the transaction and then continues immediately checking constraints of any further statements in that transaction, as long as all the checked constraints are consistent and no other SET CONSTRAINTS statement is issued. If any constraint fails the check, an error is signaled. At that point, a COMMIT causes the whole transaction to undo.

The ALTER SESSION statement also has clauses to SET CONSTRAINTS IMMEDIATE or DEFERRED. These clauses imply setting ALL deferrable constraints (that is, you cannot specify a list of constraint names). They are equivalent to making a SET CONSTRAINTS statement at the start of each transaction in the current session.

Making constraints immediate at the end of a transaction is a way of checking whether COMMIT can succeed. You can avoid unexpected rollbacks by setting constraints to IMMEDIATE as the last statement in a transaction. If any constraint fails the check, you can then correct the error before committing the transaction.

The SET CONSTRAINTS statement is disallowed inside of triggers.

SET CONSTRAINTS can be a distributed statement. Existing database links that have transactions in process are told when a SET CONSTRAINTS ALL statement occurs, and new links learn that it occurred as soon as they start a transaction.

Unique Constraints and Indexes

A user sees inconsistent constraints, including duplicates in unique indexes, when that user's transaction produces these inconsistencies. You can place deferred unique and foreign key constraints on materialized views, allowing fast and complete refresh to complete successfully.

Deferrable unique constraints always use nonunique indexes. When you remove a deferrable constraint, its index remains. This is convenient because the storage information remains available after you disable a constraint. Not-deferrable unique constraints and primary keys also use a nonunique index if the nonunique index is placed on the key columns before the constraint is enforced.