|Oracle9i Database Concepts
Release 1 (9.0.1)
Part Number A88856-02
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. The chapter includes:
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
DEPT and the business rules for the information in each of the tables, as illustrated in Figure 23-1.
Note that some columns in each table have specific rules that constrain the data contained within them.
This section describes the rules that can be applied to table columns to enforce different types of data integrity.
A null 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.
A unique value 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).
A primary key value 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.
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:
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
Set to Default
When referenced data is updated or deleted, all associated dependent data is set to a default value.
When referenced data is updated, all associated dependent data is correspondingly updated. When a referenced row is deleted, all associated dependent rows are deleted.
Disallows the update or deletion of referenced data. This differs from
Complex integrity checking is 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).
Oracle 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 integrity constraints or database triggers.
An integrity constraint is a declarative method of defining a rule for a column of a table. Oracle supports the following integrity constraints:
NOT NULLconstraints for the rules associated with nulls in a column
UNIQUE keyconstraints for the rule associated with unique column values
PRIMARY KEYconstraints for the rule associated with primary identification values
FOREIGN KEYconstraints for the rules associated with referential integrity. Oracle supports the use of
FOREIGN KEYintegrity constraints to define the referential integrity actions, including:
CHECKconstraints for complex integrity rules
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).
Chapter 18, "Triggers" for examples of triggers used to enforce data integrity
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 you want to associate with the information in a database. If any of the results of a DML statement execution violate an integrity constraint, then Oracle rolls back the statement and returns an error.
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
UPDATE statement attempts to violate this integrity constraint, then Oracle rolls back the statement and returns an information error message.
The integrity constraints implemented in Oracle fully comply with ANSI X3.135-1989 and ISO 9075-1989 standards.
This section describes some of the advantages that integrity constraints have over other alternatives, which include:
Define integrity constraints using SQL statements. When you define or alter a table, no additional programming is required. The SQL statements are easy to write and eliminate programming errors. 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 the stored procedure solution to data integrity controls data access, but integrity constraints do not eliminate the flexibility of ad hoc data access.
Integrity constraints are defined for tables (not an application) and are stored in the data dictionary. Any data entered by any application must adhere to the same integrity constraints associated with the 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. Database triggers can provide this benefit, but the complexity of implementation is far greater than the declarative approach used for integrity constraints.
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.
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.
The semantics of integrity constraint declarations are clearly defined, and 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 made when necessary.)
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 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.
You can use the following integrity constraints to impose restrictions on the input of column values:
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 example, you can define a
NOT NULL constraint to require that a value be input in the
ENAME column for every row of the
Figure 23-2 illustrates a
NOT NULL integrity constraint.
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.
For example, in Figure 23-3 a
UNIQUE key constraint is defined on the
DNAME column of the
DEPT table to disallow rows with duplicate department names.
The columns included in the definition of the
UNIQUE key constraint are called the unique key. Unique key is often incorrectly used as a synonym for the terms UNIQUE key constraint or UNIQUE index. However, note that key refers only to the column or set of columns used in the definition of the integrity constraint.
UNIQUE key consists of more than one column, that group of columns is said to be a composite unique key. For example, in Figure 23-4 the
CUSTOMER table has a
UNIQUE key constraint defined on the composite unique key: the
UNIQUE key constraint allows you to 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.
Oracle enforces unique integrity constraints with indexes. For example, in Figure 23-4, Oracle enforces the
UNIQUE key constraint by implicitly creating a unique index on the composite unique key. Therefore, composite
UNIQUE key constraints have the same limitations imposed on composite indexes: up to 32 columns can constitute a composite unique key.
If a usable index exists when a unique key constraint is created, the constraint uses that index rather than implicitly creating a new one.
In Figure 23-3 and Figure 23-4,
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 to anything. A null in a column (or in all columns of a composite
UNIQUE key) always satisfies a
UNIQUE key constraint.
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.
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:
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:
Figure 23-5 illustrates a
PRIMARY KEY constraint in the
DEPT table and examples of rows that violate the constraint.
Oracle enforces all
PRIMARY KEY constraints using indexes. In Figure 23-5, the primary key constraint created for the
DEPTNO column is enforced by the implicit creation of:
Oracle enforces primary key constraints using indexes, and 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.
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.
Several terms are associated with referential integrity constraints:
The column or set of columns included in the definition of the referential integrity constraint that reference a 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 23-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 (also the
DEPTNO column). Therefore, no erroneous department numbers can exist in the
DEPTNO column of the
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.
Another type of referential integrity constraint, shown in Figure 23-7, is called a self-referential integrity constraint. This type of foreign key references a parent key in the same table.
In Figure 23-7, 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
The relational model permits the value of foreign keys either to match 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.
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 are
DELETE No Action, and
See Chapter 18, "Triggers" for more information.
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.
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.
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
MGR in the
TMP table, then deleting a manager causes the rows for all employees working for that manager to have their
MGR value set to null.
Table 23-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|
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.
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.
Allowed if no rows in the child table reference the parent key value.
You almost always index foreign keys. The only exception is when the matching unique or primary key is never updated or deleted.
Oracle maximizes the concurrency control of parent keys in relation to dependent foreign key values. You can control what concurrency mechanisms are used to maintain these relationships, and, depending on the situation, this can be highly beneficial. The following sections explain the possible situations and give recommendations for each.
Figure 23-8 illustrates the locking mechanisms used by Oracle when no index is defined on the foreign key and when rows are being updated or deleted in the parent table. Inserts into the parent table do not require any locks on the child table.
Oracle9i, Release 1 (9.0.1), no longer requires a share lock on unindexed foreign keys when doing an update or delete on the primary key. It still obtains the table-level share lock, but then releases it immediately after obtaining it. If multiple primary keys are update or deleted, the lock is obtained and released once for each row.
In previous releases, a share lock of the entire child table was required until the transaction containing the
DELETE statement for the parent table was committed. If the foreign key specifies
ON DELETE CASCADE, then the
DELETE statement resulted in a table-level share-subexclusive lock on the child table. A share lock of the entire child table was also required for an
UPDATE statement on the parent table that affected any columns referenced by the child table. Share locks allow reading only. Therefore, no
INSERT, UPDATE, or
DELETE statements could be issued on the child table until the transaction containing the
DELETE was committed. Queries were allowed on the child table.
INSERT, UPDATE, and
DELETE statements on the child table do not acquire any locks on the parent table, although
UPDATE statements wait for a row-lock on the index of the parent table to clear.
Figure 23-9 illustrates the locking mechanisms used by Oracle when an index is defined on the foreign key, and new rows are inserted, updated, or deleted in the child table.
Notice that no table locks of any kind are acquired on the parent table or any of its indexes as a result of the insert, update, or delete. Therefore, any type of DML statement can be issued on the parent table, including inserts, updates, deletes, and queries.
This situation is preferable if there is any update or delete activity on the parent table while update activity is taking place on the child table. Inserts, updates, and deletes on the parent table do not require any locks on the child table, although updates and deletes will wait for row-level locks on the indexes of the child table to clear.
If the child table specifies
ON DELETE CASCADE, then deletes from the parent table can result in deletes from the child table. In this case, waiting and locking rules are the same as if you deleted yourself from the child table after performing the delete from the parent table.
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 results in the condition of the
CHECK constraint evaluating to false, then the statement is rolled back.
CHECK constraints enable you to enforce very specific integrity rules by specifying a check condition. The condition of a
CHECK constraint has some limitations:
SYSDATE, UID, USER,or
USERENV;or the pseudocolumns
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 uses the database's Globalization Support settings by default. You can override the defaults by specifying Globalization Support parameters explicitly in such functions within the
CHECK constraint definition.
Oracle9i Globalization Support Guide for more information on Globalization Support features
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 does not verify that
CHECK conditions are not mutually exclusive.
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:
EMPtable has been defined as in Figure 23-7.
MGRcolumn dependent on the values of the
EMPNOcolumn. For simplicity, the rest of this discussion addresses only the
MGRcolumns of the
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:
MGRcolumn of the first row, assuming that the
MGRcolumn does not have a
NOT NULLconstraint defined on it. Because nulls are allowed in foreign keys, this row is inserted successfully into the table.
MGRcolumns. This case reveals that Oracle 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
EMPNOthat corresponds to the new row's
INSERTstatement, such as an
INSERTstatement with nested
SELECTstatement, can insert rows that reference one another. For example, the first row might have
EMPNOas 200 and
MGRas 300, while the second row might have
EMPNOas 300 and
This case also shows that constraint checking is deferred until the complete execution of the statement. All rows are inserted first, then all rows are checked for constraint violations. You can also defer the checking of constraints until the end of the transaction.
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 23-10).
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 23-11 shows that Oracle performs the actions of the entire SQL statement before any constraints are checked.
The examples in this section illustrate the constraint checking mechanism during
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 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.
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.
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
Constraints can be added, dropped, enabled, disabled, or validated. You can also modify a constraint's attributes.
SET CONSTRAINTS statement makes constraints either
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.
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 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 roll back.
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.
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.
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.
You can enable or disable integrity constraints at the table level using the
CREATE TABLE or
ALTER TABLE statement. You can also set constraints to
NOVALIDATE, in any combination with
ENABLEensures that all incoming data conforms to the constraint
DISABLEallows incoming data, regardless of whether it conforms to the constraint
VALIDATEensures that existing data conforms to the constraint
NOVALIDATEmeans that some existing data may not conform to the constraint
ENABLE VALIDATEis the same as
ENABLE.The constraint is checked and is guaranteed to hold for all rows.
ENABLE NOVALIDATEmeans 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.
ALTER TABLE statement,
ENABLE NOVALIDATE resumes constraint checking on disabled constraints without first validating all data in the table.
DISABLE NOVALIDATEis the same as
DISABLE.The constraint is not checked and is not necessarily true.
DISABLE VALIDATEdisables the constraint, drops the index on the constraint, and disallows any modification of the constrained columns.
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:
NOVALIDATEdo not have any default implications for the
DISABLEstate to the
ENABLEstate, if there is no existing index, a unique index is automatically created. Similarly, when a unique or primary key moves from
DISABLEand it is enabled with a unique index, the unique index is dropped.
NOVALIDATEstate to the
VALIDATEstate, all data must be checked. (This can be very slow.) However, moving from
NOVALIDATEsimply forgets that the data was ever checked.
ENABLE NOVALIDATEstate to the
ENABLE VALIDATEstate does not block reads, writes, or other DDL statements. It can be done in parallel.
You can use the
MODIFY CONSTRAINT clause of the
ALTER TABLE statement to change the following constraint states:
USING INDEX ...
EXCEPTIONS INTO ...