| Oracle® Database Concepts
10g Release 1 (10.1) Part Number B10743-01 |
|
|
View PDF |
| Oracle® Database Concepts
10g Release 1 (10.1) Part Number B10743-01 |
|
|
View PDF |
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:
It is important that data adhere to a predefined set of rules, as determined by the database administrator or a
pplication developer. As an example of data integrity, consider the tables employees and departments and th
e business rules for the information in each of the tables, as illustrated in Figure 21-1.
Note that some columns in each table have specific rules that constrain the data contained withi n them.
This section describes the rules that can be appli ed to table columns to enforce different types of data integrity.
A null rule is a rule defined on a single colum n that allows or disallows inserts or updates of rows containing a null (the absence of a value) in that column.
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).
A primary key value rule defined on a key (a column or set of columns) specifies that each r ow in the table can be uniquely identified by the values in the key.
A referential integrity rule is a rule defined o n 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 a llowed on referenced values and how these actions affect dependent values. The rules associated with referential integrity are:
< ul>Restrict: Disallows the update or deletion of referenced data.
Set to Null: When referenced data is updated or deleted, all associa
ted 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.
Ca scade: When referenced data is updated, all associated dependent data is correspondingly upd ated. 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 uses No Action as its default action.)
Oracle enables you to define and enforce each type of data integrity rule defined in t he 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 NULL constraints for the rules associated with nulls in a column
UNIQUE key constraints for
the rule associated with unique column values
PRIMARY KEY constraints for the
rule associated with primary identification values
FOREIGN KEY constraints fo
r the rules associated with referential integrity. Oracle supports the use of FOREIGN KEY integrity constra
ints to define the referential integrity actions, including:
Update and delete No Action
Delete CASCADE
Delete SET NULL
CHECK constraints for complex integrity rules
|
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). |
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 salary column of the employe
es table. This integrity constraint enforces the rule that no row in this table can contain a numeric value greater than 10,00
0 in this column. If an INSERT or UPDATE statement attempts to violate this integrity constraint, then Orac
le 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:
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
Define integrity constraints using SQL statements. When you defi ne or alter a table, no additional programming is required. The SQL statements are easy to write and eliminate programming errors. Or acle controls their functionality. For these reasons, declarative integrity constraints are preferable to application code and databa se triggers. The declarative approach is also better than using stored procedures, because the stored procedure solution to data inte grity 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 dictio nary. 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 rul es stored with a table. Database triggers can provide this benefit, but the complexity of implementation is far greater than the decl arative 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 eac h database application, developers would have to modify all application source code and recompile, debug, and test the modified appli cations.
Oracle stores specific information about each integrity constraint in the data dictionary. You can design databa se applications to use this information to provide immediate user feedback about integrity constraint violations, even before Oracle runs and checks the SQL statement. For example, an Oracle Forms application can use integrity constraint definitions stored in the da ta 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 optimizer can use declarations to learn more about data to improve overall query performan ce. (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 re port any new rows that violate integrity constraints to a separate exceptions table.
You can use the following int egrity 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 value
s. For example, you can define a NOT NULL constraint to require that a value be input in the last_nam
e column for every row of the employees table.
Figure 21-2 illustrates a NOT NULL integrity constraint.
A UNIQUE key integrity constraint requires that every val
ue 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 o
r set of columns.
For example, in Figure 21-3 a UNIQUE key constraint is defined on 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<
/strong> 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 constra
int.
If the UNIQUE key consists of more than one column, then that group of columns is said to be a composite uniqu
e key. For example, in Figure 21-4 the customer table has a UNIQUE key constr
aint defined on the composite unique key: the area and phone columns.
This UNIQUE key constraint lets you enter an area code and telephone number any number of ti
mes, but the combination of a given area code and given telephone number cannot be duplicated in the table. This eliminates unintenti
onal duplication of a telephone number.
Oracle enforces unique integrity constraints with indexes. For example, in Figure 21-4, Oracle enforces the UNIQUE
key constraints have the same limitations imposed on composite indexes: up to 32 columns can constitute a composite unique ke
y.
|
Note: If compatibility is set to Oracle9i or higher, then the total size in bytes of a key value can be almost as large as a full block. In previo us releases key size could not exceed approximately half the associated database's block size. |
If a usable index exists when a unique key constraint is created, the constraint uses that inde x rather than implicitly creating a new one.
In Figure 21-3 and Figure 21-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 UNIQ
UE key) always satisfies a UNIQUE key constraint.
Columns with bo
th unique keys and NOT NULL integrity constraints are common. This combination forces the user to enter val
ues 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 grou
p 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 tha
t both of the following are true:
No two rows of a table have duplicate values in the specified column o r set of columns.
The primary key columns do not allow nulls. That is, a value must exist for the primar y key columns in each row.
The columns included in the definition of a table's PRIMARY KEY integrity constraint are called th
e 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 21-5 illustrates a PRIMARY KEY constraint in the dept table and example
s of rows that violate the constraint.
Oracle enforces all PRIM
ARY KEY constraints using indexes. In Figure 21-5, the primary key constraint created for th
e deptno column is enforced by the implicit creation of:
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 CREATE TABLE or ALTER TABLE statement used to creat
e 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 go vern the relationship of the columns must be maintained. Referential integrity rules guarantee that these relationships are preserved .
The following terms are associated with referential integrity constraints.
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-6 shows a foreign key defined on the deptno column of the <
code>emp table. It guarantees that every value in this column must match a value in the primary key of the dept t
able (also the deptno column). Therefore, no erroneous department numbers can exist in the deptno column of
the emp table.
Foreign keys ca n be defined as multiple columns. However, a composite foreign key must reference a composite primary or unique key with the same num ber of columns and the same datatypes. Because composite primary and unique keys are limited to 32 columns, a composite foreign key i s also limited to 32 columns.
Anothe r type of referential integrity constraint, shown in Figure 21-7, is called a self-referential integrity constra int. This type of foreign key references a parent key in the same table.
In Figure 21-7, the referential
integrity constraint ensures that every value in the mgr column of the emp table corresponds to a value tha
t currently exists in the empno column of the same table, but not necessarily in the same row, because every manager mus
t also be an employee. This integrity constraint eliminates the possibility of erroneous employee numbers in the mgr col
umn.
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 no n-null portions of the key do not have to match any corresponding portion of a parent key.
<
a id="sthref3082" name="sthref3082">Referential integrity constraints can specify partic
ular actions to be performed on the dependent rows in a child table if a referenced parent key value is modified. The referential act
ions supported by the FOREIGN KEY integrity constraints of Oracle are UPDATE and DELETE<
/code> NO ACTION, and DELETE CASCADE.
|
Note: Other referential actions not supported byFOREIGN KEY integrity constraints of Oracle can be enforced using database triggers.
See Cha pter 22, " Triggers " for more information. |
The No Action (default) option speci fies 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 dele ted because of the dependent data.
A delete cascades when rows containin g referenced key values are deleted, causing all rows in child tables with dependent foreign key values to also be deleted. For examp le, 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 chil d 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, c
ausing 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 workin
g for that manager to have their manager_id value set to null.
Table 21-1 outlines the DML statements allowed by the diffe rent referential actions on the primary/unique key values in the parent table, and the foreign key values in the child table.
Table 21-1 DML Statements Allowed by Update and Delete No Action
| DML Statement | Issued Against Parent Table | < th align="left" valign="bottom" id="r1c3-t10">Issued Against Child Table|
|---|---|---|
INSERT<
/td>
| 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. |
D
ELETE Cascade |
Always OK. | A lways OK. |
DELETE Set Nu
ll |
Always OK. | Always OK. |
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 fore ign 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 21-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 o n the child table.
Unindexed foreign keys cause DML on the primary key to get a share row exclusive table lock (also sometimes called a share-subexclusive table lock, SSX) on the foreign key table. This prevents DML on the table by other tran sactions. The SSX lock is released immediately after it is obtained. If multiple primary keys are updated or deleted, the lock is obt ained and released once for each row.
|
Note: Indexed foreign keys only cause a row share table lock (also sometimes called a subshare table lo ck, SS). This prevents other transactions from exclusive locking the whole table, but it does not block DML on the parent or the child table. |
Figure 21-8 Locking Mechanisms When No Index Is Defined on the Foreign Key
< /strong>Figure 21-9 illustrate s the locking mechanisms used when an index is defined on the foreign key, and new rows are inserted, updated, or deleted in the chil d table.
Indexed foreign keys cause a row share table lock (also sometimes called a subshare table lock, SS). This prevents other transactions from exclusive locking the whole table, but it does not block DML on the parent or the chi ld table.
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, altho ugh updates and deletes will wait for row-level locks on the indexes of the child table to clear.
Figure 21-9 L ocking Mechanisms When Index Is Defined on the Foreign Key
If the child table specifies ON DELETE CASCAD
E, 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.
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 results in the condition of the CHECK constr
aint evaluating to false, then the statement is rolled back.
CHECK constraints let you enf
orce very specific integrity rules by specifying a check condition. The condition of a CHECK constraint has some limitat
ions:
It must be a Boolean expression evaluated using the values in the row being inserted or updated, a nd
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 uses the database globalization su
pport 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 Glob alization Support Guide for more information on globalization support features |
To know what types of actions are permitted when constraints are present, it is useful to understand whe n Oracle actually performs the checking of constraints. Assume the following:
The emp table
has been defined as in Figure 21-7.
The self-referential constraint makes the entr
ies in the mgr column dependent on the values of the empno column. For simplicity, the rest of this discuss
ion addresses only the empno and mgr columns of the emp table.
Consider the i
nsertion of the first row into the emp table. No rows currently exist, so how can a row be entered if the value in the <
code>mgr column cannot reference any existing value in the empno column? Three possibilities for doing this are:<
/p>
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. Because nulls are allowed in foreign keys
, this row is inserted successfully into the table.
The same value can be entered in both the empn
o and mgr columns. This case reveals that Oracle performs its constraint checking after the statement ha
s been completely run. To allow a row to be entered with the same values in the parent key and the foreign key, Oracle must first run
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.
A multiple row INSERT statement, such as an INSER
T 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.
This case also shows that constraint checking is deferred until the complete execution of the statement. A ll 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 scenar io. The company has been sold. Because of this sale, all employee numbers must be updated to be the current value plus 5000 to coordi nate with the new company's employee numbers. Because manager numbers are really employee numbers, these values must also increase by 5000 (see Figure 21-10).
UP
DATE 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 eff
ectively performs its constraint checking after the statement completes. Figure 21-11 shows that Oracle performs
the actions of the entire SQL statement before any constraints are checked.
Figure 21-11 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 f ollowing:
NOT NULL
UNIQUE key
PRIMARY KEY
All types of FOREIGN KEY
constraints
CHECK constraints
You can defer checking constraints for validity until the end of the transaction.
A constraint is deferred if the system checks that it is satisfied only on commit. If a deferred constra int is violated, then commit causes the transaction to undo.
If a constraint is immediate (not deferred), then it is checked at the end of each statement. If it is violated, the s tatement is rolled back immediately.
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 def
errable, and either initially deferred or initially immediate. These attributes can be dif
ferent 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 validat ed. You can also modify a constraint's attributes.
|
See Also:
|
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 first checks any constraints that
were deferred earlier in the transaction and then continues immediately checking constraints of any further statements in that trans
action, as long as all the checked constraints are consistent and no other SET CONSTRAINTS statement is iss
ued. If any constraint fails the check, an error is signaled. At that point, a COMMIT causes the whole transaction to un
do.
The ALTER SESSION statement also has clauses to S
ET CONSTRAINTS IMMEDIATE or DEFERRED. These clauses imply setting ALL defe
rrable constraints (that is, you cannot specify a list of constraint names). They are equivalent to making a SET C
ONSTRAINTS 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 in
side 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 le
arn that it occurred as soon as they start a transaction.
A use r 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 succ essfully.
Deferrable unique constraints always use nonunique indexes. When you remove a deferrable constraint, its index remai ns. This is convenient because the storage information remains available after you disable a constraint. Not-deferrable unique constr aints and primary keys also use a nonunique index if the nonunique index is placed on the key columns before the constraint is enforc ed.
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 m
eans that some existing data may not conform to the constraint
In addition:
ENABL
E VALIDATE is the same as ENABLE. The constraint is checked and is guaranteed to hold for all rows.<
/p>
ENABLE NOVALIDATE means that the constraint is checked, but it does not have t
o be true for all rows. This allows existing rows to violate the constraint, while ensuring that all new or modified rows are valid.<
/p>
In an ALTER TABLE statement, ENABLE NOVALIDATE resumes constraint checking
on disabled constraints without first validating all data in the table.
DISABLE NOVA
LIDATE 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 modifi
cation 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 PARTI
TION clause of the ALTER TABLE statement.
Transitions between these states are gove rned by the following rules:
ENABLE implies VALIDATE, unless NOVALIDATE<
/code> 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 ENABLE state, if there is no existing index, a unique index is automatically created. Si
milarly, when a unique or primary key moves from ENABLE to DISABLE and it is enabled with a unique index, t
he unique index is dropped.
When any constraint is moved from the NOVALIDATE state to the <
code>VALIDATE state, all data must be checked. (This can be very slow.) However, moving from VALIDATE to NO
VALIDATE 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.
You can use th
e MODIFY CONSTRAINT clause of the ALTER TABLE statement to change the following c
onstraint states:
DEFERRABLE or NOT DEFERRABLE
INITIALLY DEFERRED or INITIALLY IMMEDIATE
RELY or NORELY
USING INDEX ...
ENABLE or DISABLE
VALIDATE or NOVALIDATE
EXCEPTIONS INTO ...
|
 Copyright © 1993, 2003 Oracle Corporation All Rights Reserved. |
|
