Oracle8 Application Developer's Guide Release 8.0 A58241-01 |
Library | Product | Contents | Index

# 9
Maintaining Data Integrity

This chapter explains how to enforce the business rules associated with your database and prevent the entry of invalid information into tables by using integrity constraints. Topics include the following:

- Using Integrity Constraints
- Referential Integrity in a Distributed Database
- Using CHECK Integrity Constraints
- Defining Integrity Constraints
- Enabling and Disabling Integrity Constraints
- Altering Integrity Constraints
- Dropping Integrity Constraints
- Managing FOREIGN KEY Integrity Constraints
- Listing Integrity Constraint Definitions

  See Also: Trusted Oracle documentation for additional information about defining, enabling, disabling, and dropping integrity constraints in Trusted Oracle.

## Using Integrity Constraints

You can define integrity constraints to enforce business rules on data in your tables. Once an integrity constraint is enabled, all data in the table must conform to the rule that it specifies. If you subsequently issue a SQL statement that modifies data in the table, Oracle ensures that the resulting data satisfies the integrity constraint. Without integrity constraints, such business rules must be enforced programmatically by your application.

### When to Use Integrity Constraints

Enforcing rules with integrity constraints is less costly than enforcing the equivalent rules by issuing SQL statements in your application. The semantics of integrity constraints are very clearly defined, so the internal operations that Oracle performs to enforce them are optimized beneath the level of SQL statements in Oracle. Since your applications use SQL, they cannot achieve this level of optimization.

Enforcing business rules with SQL statements can be even more costly in a networked environment because the SQL statements must be transmitted over a network. In such cases, using integrity constraints eliminates the performance overhead incurred by this transmission.

##### Example

To ensure that each employee in the `EMP` table works for a department that is listed in the `DEPT` table, first create a `PRIMARY` `KEY` constraint on the `DEPTNO` column of the `DEPT` table with this statement:

    ALTER TABLE dept

    ADD PRIMARY KEY (deptno)

Then create a referential integrity constraint on the `DEPTNO` column of the `EMP` table that references the primary key of the `DEPT` table:

    ALTER TABLE emp

    ADD FOREIGN KEY (deptno) REFERENCES dept(deptno)

If you subsequently add a new employee record to the table, Oracle automatically ensures that its department number appears in the department table.

To enforce this rule without integrity constraints, your application must test each new employee record to ensure that its department number belongs to an existing department. This testing involves issuing a `SELECT` statement to query the `DEPT` table.

### Taking Advantage of Integrity Constraints

For best performance, define and enable integrity constraints and develop your applications to rely on them, rather than on SQL statements in your applications, to enforce business rules.

However, in some cases, you might want to enforce business rules through your application as well as through integrity constraints. Enforcing a business rule in your application might provide faster feedback to the user than an integrity constraint. For example, if your application accepts 20 values from the user and then issues an `INSERT` statement containing these values, you might want your user to be notified immediately after entering a value that violates a business rule.

Since integrity constraints are enforced only when a SQL statement is issued, an integrity constraint can only notify the user of a bad value after the user has entered all 20 values and the application has issued the `INSERT` statement. However, you can design your application to verify the integrity of each value as it is entered and notify the user immediately in the event of a bad value.

### Using NOT NULL Integrity Constraints

By default, all columns can contain nulls. Only define `NOT` `NULL` constraints for columns of a table that absolutely require values at all times.

For example, in the `EMP` table, it might not be detrimental if an employee's manager or hire date were temporarily omitted. Also, some employees might not have a commission. Therefore, these three columns would not be good candidates for `NOT` `NULL` integrity constraints. However, it might not be permitted to have a row that does not have an employee name. Therefore, this column is a good candidate for the use of a `NOT` `NULL` integrity constraint.

`NOT NULL` constraints are often combined with other types of integrity constraints to further restrict the values that can exist in specific columns of a table. Use the combination of `NOT NULL` and `UNIQUE` key integrity constraints to force the input of values in the `UNIQUE` key; this combination of data integrity rules eliminates the possibility that any new row's data will ever attempt to conflict with an existing row's data. For more information about such combinations.

See Also: "Relationships Between Parent and Child Tables" on page 9-9.

#### Figure 9-1 NOT NULL Integrity Constraints

### Setting Default Column Values

Legal default values include any literal, or any expression that does not refer to a column, `LEVEL`, `ROWNUM`, or `PRIOR`. Default values can include the expressions `SYSDATE`, `USER`, `USERENV`, and `UID`. The datatype of the default literal or expression must match or be convertible to the column datatype.

If you do not explicitly define a default value for a column, the default for the column is implicitly set to `NULL`.

#### When to Use Default Values

Only assign default values to columns that contain a typical value. For example, in the `DEPT` table, if most departments are located at one site, the default value for the `LOC` column can be set to this value (such as `NEW YORK`).

Defaults are also useful when you use a view to make a subset of a table's columns visible. For example, you might allow users to insert rows into a table through a view. The view is defined to show all columns pertinent to end-user operations; however, the base table might also have a column named `INSERTER`, not included in the definition of the view, which logs the user that originally inserts each row of the table. The column named `INSERTER` can record the name of the user that inserts a row by defining the column with the `USER` function:

    . . ., inserter VARCHAR2(30) DEFAULT USER, . . .

For another example of assigning a default column value, refer to the section "Creating Tables" on page 4-3.

#### Figure 9-2 A UNIQUE Key Constraint

### Choosing a Table's Primary Key

Each table can have one primary key. A primary key allows each row in a table to be uniquely identified and ensures that no duplicate rows exist. Use the following guidelines when selecting a primary key:

- Choose a column whose data values are unique.

  The purpose of a table's primary key is to uniquely identify each row of the table. Therefore, the column or set of columns in the primary key must contain unique values for each row.

- Choose a column whose data values are never changed.

  A primary key value is only used to identify a row in the table; primary key values should never contain any data that is used for any other purpose. Therefore, primary key values should rarely need to be changed.

- Choose a column that does not contain any nulls.

  A `PRIMARY KEY` constraint, by definition, does not allow the input of any row with a null in any column that is part of the primary key.

- Choose a column that is short and numeric.

  Short primary keys are easy to type. You can use sequence numbers to easily generate numeric primary keys.

- Avoid choosing composite primary keys.

  Although composite primary keys are allowed, they do not satisfy the previous recommendations. For example, composite primary key values are long and cannot be assigned by sequence numbers.

### Using UNIQUE Key Integrity Constraints

Choose unique keys carefully. In many situations, unique keys are incorrectly comprised of columns that should be part of the table's primary key (see the previous section for more information about primary keys). When deciding whether to use a UNIQUE key constraint, use the rule that a UNIQUE key constraint is only required to prevent the duplication of the key values within the rows of the table. The data in a unique key is such that it cannot be duplicated in the table.

Note: Although `UNIQUE` key constraints allow the input of nulls, 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.

Do not confuse the concept of a unique key with that of a primary key. Primary keys are used to identify each row of the table uniquely. Therefore, unique keys should not have the purpose of identifying rows in the table.

Some examples of good unique keys include

- an employee's social security number (the primary key is the employee number)
- a truck's license plate number (the primary key is the truck number)
- a customer's phone number, consisting of the two columns `AREA` and `PHONE` (the primary key is the customer number)
- a department's name and location (the primary key is the department number)

## Using Referential Integrity Constraints

Whenever two tables are related by a common column (or set of columns), define a `PRIMARY` or `UNIQUE` key constraint on the column in the parent table, and define a `FOREIGN` `KEY` constraint on the column in the child table, to maintain the relationship between the two tables. Depending on this relationship, you may want to define additional integrity constraints including the foreign key, as listed in the section "Relationships Between Parent and Child Tables" on page 9-9.

Figure 9-3 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 of the exact same structure; that is, 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.

### Nulls and Foreign Keys

By default (that is, without any `NOT` `NULL` or `CHECK` clauses), and in accordance with the ANSI/ISO standard, the `FOREIGN` `KEY` constraint enforces the "match none" rule for composite foreign keys. The "full" and "partial" rules can also be enforced by using `CHECK` and `NOT` `NULL` constraints, as follows:

- To enforce the "match full" rule for nulls in composite foreign keys, which requires that all components of the key be null or all be non-null, define a `CHECK` constraint that allows only all nulls or all non-nulls in the composite foreign key as follows, assuming a composite key comprised of columns A, B, and C:

      CHECK ((A IS NULL AND B IS NULL AND C IS NULL) OR
             (A IS NOT NULL AND B IS NOT NULL AND C IS NOT NULL))

- In general, it is not possible to use declarative referential integrity to enforce the "match partial" rule for nulls in composite foreign keys, which requires the non-null portions of the key to appear in the corresponding portions in the primary or unique key of a single row in the referenced table. You can often use triggers to handle this case, as described in Chapter 13, "Using Database Triggers".

  #### Figure 9-3 Referential Integrity Constraints

### Relationships Between Parent and Child Tables

Several relationships between parent and child tables can be determined by the other types of integrity constraints defined on the foreign key in the child table.

##### No Constraints on the Foreign Key

When no other constraints are defined on the foreign key, any number of rows in the child table can reference the same parent key value. This model allows nulls in the foreign key.

This model establishes a "one-to-many" relationship between the parent and foreign keys that allows undetermined values (nulls) in the foreign key. An example of such a relationship is shown in Figure 9-3 between `EMP` and `DEPT`; each department (parent key) has many employees (foreign key), and some employees might not be in a department (nulls in the foreign key).

##### NOT NULL Constraint on the Foreign Key

When nulls are not allowed in a foreign key, each row in the child table must explicitly reference a value in the parent key because nulls are not allowed in the foreign key. However, any number of rows in the child table can reference the same parent key value.

This model establishes a "one-to-many" relationship between the parent and foreign keys. However, each row in the child table must have a reference to a parent key value; the absence of a value (a null) in the foreign key is not allowed. The same example in the previous section can be used to illustrate such a relationship. However, in this case, employees must have a reference to a specific department.

##### UNIQUE Constraint on the Foreign Key

When a `UNIQUE` constraint is defined on the foreign key, one row in the child table can reference a parent key value. This model allows nulls in the foreign key.

This model establishes a "one-to-one" relationship between the parent and foreign keys that allows undetermined values (nulls) in the foreign key. For example, assume that the `EMP` table had a column named `MEMBERNO`, referring to an employee's membership number in the company's insurance plan. Also, a table named `INSURANCE` has a primary key named `MEMBERNO`, and other columns of the table keep respective information relating to an employee's insurance policy. The `MEMBERNO` in the `EMP` table should be both a foreign key and a unique key:

- to enforce referential integrity rules between the `EMP` and `INSURANCE` tables (the `FOREIGN KEY` constraint)
- to guarantee that each employee has a unique membership number (the `UNIQUE` key constraint)

##### UNIQUE and NOT NULL Constraints on the Foreign Key

When both `UNIQUE` and `NOT` `NULL` constraints are defined on the foreign key, only one row in the child table can reference a parent key value. Because nulls are not allowed in the foreign key, each row in the child table must explicitly reference a value in the parent key.

This model establishes a "one-to-one" relationship between the parent and foreign keys that does not allow undetermined values (nulls) in the foreign key. If you expand the previous example by adding a `NOT` `NULL` constraint on the `MEMBERNO` column of the EMP table, in addition to guaranteeing that each employee has a unique membership number, you also ensure that no undetermined values (nulls) are allowed in the `MEMBERNO` column of the `EMP` table.

### Multiple FOREIGN KEY Constraints

Oracle allows a column to be referenced by multiple `FOREIGN` `KEY` constraints; effectively, there is no limit on the number of dependent keys. This situation might be present if a single column is part of two different composite foreign keys.

### Concurrency Control, Indexes, and Foreign Keys

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.

##### No Index on the Foreign Key

Figure 9-4 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.

Notice that a share lock of the entire child table is required until the transaction containing the `DELETE` statement for the parent table is committed. If the foreign key specifies `ON DELETE CASCADE,` the `DELETE` statement results in a table-level share-subexclusive lock on the child table. A share lock of the entire child table is also required for an `UPDATE` statement on the parent table that affects any columns referenced by the child table. Share locks allow reading only; therefore, no `INSERT`, `UPDATE`, or `DELETE` statements can be issued on the child table until the transaction containing the `UPDATE` or `DELETE` is committed. Queries are allowed on the child table.

This situation is tolerable if updates and deletes can be avoided on the parent.

`INSERT`, `UPDATE`, and `DELETE` statements on the child table do not acquire any locks on the parent table; although `INSERT` and `UPDATE` statements will wait for a row-lock on the index of the parent table to clear.

#### Figure 9-4 Locking Mechanisms When No Index Is Defined on the Foreign Key

##### Index on the Foreign Key

Figure 9-5 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.

#### Figure 9-5 Locking Mechanisms When Index Is Defined on the Foreign Key

If the child table specifies `ON DELETE CASCADE`, deletes from the parent table may result in deletes from the child table. In this case, waiting and locking rules are the same as if you deleted from the child table yourself after performing the delete from the parent table.

## Referential Integrity in a Distributed Database

Oracle does not permit declarative referential integrity constraints to be defined across nodes of a distributed database (that is, a declarative referential integrity constraint on one table cannot specify a foreign key that references a primary or unique key of a remote table). However, parent/child table relationships across nodes can be maintained using triggers. For more information about triggers that enforce referential integrity, refer to Chapter 13, "Using Database Triggers". Using triggers to maintain referential integrity requires the distributed option; for more information refer to Oracle8 Distributed Database Systems

Note: If you decide to define referential integrity across the nodes of a distributed database using triggers, be aware that network failures can limit the accessibility of not only the parent table, but also the child table. For example, assume that the child table is in the `SALES` database and the parent table is in the `HQ` database. If the network connection between the two databases fails, some DML statements against the child table (those that insert rows into the child table or update a foreign key value in the child table) cannot proceed because the referential integrity triggers must have access to the parent table in the `HQ` database.

## Using CHECK Integrity Constraints

Use CHECK constraints when you need to enforce integrity rules that can be evaluated based on logical expressions. Never use CHECK constraints when any of the other types of integrity constraints can provide the necessary checking.

See Also: "CHECK and NOT NULL Integrity Constraints" on page 9-15.

Examples of appropriate CHECK constraints include the following:

- a `CHECK` constraint on the `SAL` column of the `EMP` table so that no salary value is greater than 10000
- a `CHECK` constraint on the `LOC` column of the `DEPT` table so that only the locations "`BOSTON`", "`NEW YORK`", and "`DALLAS`" are allowed
- a `CHECK` constraint on the `SAL` and `COMM` columns to compare the SAL and `COMM` values of a row and prevent the `COMM` value from being greater than the `SAL` value

### Restrictions on CHECK Constraints

A `CHECK` integrity constraint requires that a condition be true or unknown for every row of the table. If a statement causes the condition to evaluate to false, the statement is rolled back. The condition of a CHECK constraint has the following limitations:

- The condition must be a Boolean expression that can be evaluated using the values in the row being inserted or updated.
- The condition cannot contain subqueries or sequences.
- The condition cannot include the `SYSDATE`, `UID`, `USER`, or `USERENV` SQL functions.
- The condition cannot contain the pseudocolumns `LEVEL`, `PRIOR`, or `ROWNUM`;

  See Also: Oracle8 SQL Reference for an explanation of these pseudocolumns.

- The condition cannot contain a user-defined SQL function.

### Designing CHECK Constraints

When using CHECK constraints, consider the ANSI/ISO standard, which states that a CHECK constraint is violated only if the condition evaluates to false; true and unknown values do not violate a check condition. Therefore, make sure that a CHECK constraint that you define actually enforces the rule you need enforced.

For example, consider the following `CHECK` constraint:

    CHECK (sal > 0 OR comm >= 0)

At first glance, this rule may be interpreted as "do not allow a row in the `EMP` table unless the employee's salary is greater than zero or the employee's commission is greater than or equal to zero." However, note that if a row is inserted with a null salary and a negative commission, the row does not violate the `CHECK` constraint because the entire check condition is evaluated as unknown. In this particular case, you can account for such violations by placing `NOT` `NULL` integrity constraints on both the `SAL` and `COMM` columns.

Note: If you are not sure when unknown values result in `NULL` conditions, review the truth tables for the logical operators `AND` and `OR` in Oracle8 SQL 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 can be defined that reference a column.

### CHECK and NOT NULL Integrity Constraints

According to the ANSI/ISO standard, a `NOT NULL` integrity constraint is an example of a `CHECK` integrity constraint, where the condition is

    CHECK (column_name IS NOT NULL)

Therefore, `NOT NULL` integrity constraints for a single column can, in practice, be written in two forms: using the `NOT NULL` constraint or a `CHECK` constraint. For ease of use, you should always choose to define `NOT NULL` integrity constraints instead of `CHECK` constraints with the "`IS NOT NULL`" condition.

In the case where a composite key can allow only all nulls or all values, you must use a `CHECK` integrity constraint. For example, the following expression of a `CHECK` integrity constraint allows a key value in the composite key made up of columns `C1` and `C2` to contain either all nulls or all values:

    CHECK ((c1 IS NULL AND c2 IS NULL) OR

    (c1 IS NOT NULL AND c2 IS NOT NULL))

## Defining Integrity Constraints

Define an integrity constraint using the constraint clause of the SQL commands `CREATE TABLE` or `ALTER TABLE`. The next two sections describe how to use these commands to define integrity constraints.

Note: There are additional considerations if you are using Trusted Oracle; see the Trusted Oracle for more information.

### The CREATE TABLE Command

The following examples of `CREATE TABLE` statements show the definition of several integrity constraints:

    CREATE TABLE dept (

    deptno  NUMBER(3) PRIMARY KEY,
    dname   VARCHAR2(15),
    loc     VARCHAR2(15),
            CONSTRAINT dname_ukey UNIQUE (dname, loc),
            CONSTRAINT loc_check1
                    CHECK (loc IN ('NEW YORK', 'BOSTON', 'CHICAGO')));

    CREATE TABLE emp (

    empno    NUMBER(5) PRIMARY KEY,
    ename    VARCHAR2(15) NOT NULL,
    job      VARCHAR2(10),
    mgr      NUMBER(5) CONSTRAINT mgr_fkey
                     REFERENCES emp,
    hiredate DATE,
    sal      NUMBER(7,2),
    comm     NUMBER(5,2),
    deptno   NUMBER(3) NOT NULL

       CONSTRAINT dept_fkey
           REFERENCES dept ON DELETE CASCADE);

### The ALTER TABLE Command

You can also define integrity constraints using the constraint clause of the ALTER TABLE command. For example, the following examples of ALTER TABLE statements show the definition of several integrity constraints:

    ALTER TABLE dept

    ADD PRIMARY KEY (deptno);

    ALTER TABLE emp

    ADD CONSTRAINT dept_fkey FOREIGN KEY (deptno) REFERENCES dept
    MODIFY (ename VARCHAR2(15) NOT NULL);

#### Restrictions with the ALTER TABLE Command

Because data is likely to be in the table at the time an ALTER TABLE statement is issued, there are several restrictions to be aware of. Table 9-1 lists each type of constraint and the associated restrictions with the ALTER TABLE command.

##### Table 9-1 Restrictions for Defining Integrity Constraints with the ALTER TABLE Command

Type of Constraint | Added to Existing Columns of the Table | Added with New Columns to the Table
--- | --- | ---
`NOT NULL` | Cannot be defined if any row contains a null value for this column* | Cannot be defined if the table contains any rows
`UNIQUE` | Cannot be defined if duplicate values exist in the key* | Always OK
`PRIMARY KEY` | Cannot be defined if duplicate or null values exist in the key* | Cannot be defined if the table contains any rows
`FOREIGN KEY` | Cannot be defined if the foreign key has values that do not reference a parent key value* | Always OK
`CHECK` | Cannot be defined if the volume has values that do not comply with the check condition* | Always OK

* Assumes DISABLE clause not included in statement.

If you attempt to define a constraint with an `ALTER TABLE` statement and violate one of these restrictions, the statement is rolled back and an informative error is returned explaining the violation.

### Required Privileges

The creator of a constraint must have the ability to create tables (that is, the `CREATE` `TABLE` or `CREATE ANY TABLE` system privilege) or the ability to alter the table (that is, the ALTER object privilege for the table or the `ALTER ANY TABLE` system privilege) with the constraint. Additionally, `UNIQUE` key and `PRIMARY KEY` integrity constraints require that the owner of the table have either a quota for the tablespace that contains the associated index or the `UNLIMITED TABLESPACE` system privilege. `FOREIGN KEY` integrity constraints also require some additional privileges.

See Also: "Privileges Required for FOREIGN KEY Integrity Constraints" on page 9-26 for specific information.

### Naming Integrity Constraints

Assign names to `NOT` `NULL,` `UNIQUE` `KEY,` `PRIMARY` `KEY,` `FOREIGN` `KEY,` and `CHECK` constraints using the `CONSTRAINT` option of the constraint clause. This name must be unique with respect to other constraints that you own. If you do not specify a constraint name, one is assigned by Oracle.

See the previous examples of the `CREATE` `TABLE` and `ALTER` `TABLE` statements for examples of the `CONSTRAINT` option of the Constraint clause. Note that the name of each constraint is included with other information about the constraint in the data dictionary.

See Also: "Listing Integrity Constraint Definitions" on page 9-27 for examples of data dictionary views.

### Enabling and Disabling Constraints Upon Definition

By default, whenever an integrity constraint is defined in a `CREATE` or `ALTER` TABLE statement, the constraint is automatically enabled (enforced) by Oracle unless it is specifically created in a disabled state using the `DISABLE` clause.

See Also: "Enabling and Disabling Key Integrity Constraints" on page 9-22 for more information about important issues for enabling and disabling constraints.

### UNIQUE Key, PRIMARY KEY, and FOREIGN KEY

When defining `UNIQUE` key, `PRIMARY` `KEY`, and `FOREIGN` `KEY` integrity constraints, you should be aware of several important issues and prerequisites. For information about defining and managing `FOREIGN` `KEY` constraints

See Also: "Managing FOREIGN KEY Integrity Constraints" on page 9-25. UNIQUE key and PRIMARY KEY constraints are usually enabled by the database administrator, and the Oracle8 Administrator's Guide .

## Enabling and Disabling Integrity Constraints

This section explains the mechanisms and procedures for manually enabling and disabling integrity constraints.

enabled constraint | When a constraint is enabled, the rule defined by the constraint is enforced on the data values in the columns that define the constraint. The definition of the constraint is stored in the data dictionary.
disabled constraint | When a constraint is disabled, the rule defined by the constraint is not enforced on the data values in the columns included in the constraint; however, the definition of the constraint is retained in the data dictionary.

In summary, an integrity constraint can be thought of as a statement about the data in a database. This statement is always true when the constraint is enabled; however, the statement may or may not be true when the constraint is disabled because data in violation of the integrity constraint can be in the database.

### Why Enable or Disable Constraints?

To enforce the rules defined by integrity constraints, the constraints should always be enabled; however, in certain situations, it is desirable to disable the integrity constraints of a table temporarily for performance reasons. For example:

- when loading large amounts of data into a table using SQL*Loader
- when performing batch operations that make massive changes to a table (such as changing everyone's employee number by adding 1000 to the existing number)
- when importing or exporting one table at a time

In cases such as these, integrity constraints may be temporarily turned off to improve the performance of the operation.

### Integrity Constraint Violations

If a row of a table does not adhere to an integrity constraint, this row is said to be in violation of the constraint and is known as an exception to the constraint. If any exceptions exist, the constraint cannot be enabled. The rows that violate the constraint must be either updated or deleted in order for the constraint to be enabled.

Exceptions for a specific integrity constraint can be identified while attempting to enable the constraint. This procedure is discussed in the section "Exception Reporting" on page 9-23.

### On Definition

When you define an integrity constraint in a `CREATE` `TABLE` or `ALTER` `TABLE` statement, you can enable the constraint by including the `ENABLE` clause in its definition or disable it by including the `DISABLE` clause in its definition. If neither the `ENABLE` nor the `DISABLE` clause is included in a constraint's definition, Oracle automatically enables the constraint.

#### Enabling Constraints

The following `CREATE` `TABLE` and `ALTER` `TABLE` statements both define and enable integrity constraints:

    CREATE TABLE emp (
        empno NUMBER(5) PRIMARY KEY,   . . . );
     ALTER TABLE emp
        ADD PRIMARY KEY (empno);

An ALTER TABLE statement that defines and attempts to enable an integrity constraint may fail because rows of the table may violate the integrity constraint. In this case, the statement is rolled back and the constraint definition is not stored and not enabled. Refer to the section "Exception Reporting" on page 9-23 for more information about rows that violate integrity constraints.

#### Disabling Constraints

The following CREATE TABLE and ALTER TABLE statements both define and disable integrity constraints:

    CREATE TABLE emp (

    empno NUMBER(5) PRIMARY KEY DISABLE,   . . . );

    ALTER TABLE emp

    ADD PRIMARY KEY (empno) DISABLE;

An `ALTER` `TABLE` statement that defines and disables an integrity constraints never fails. The definition of the constraint is always allowed because its rule is not enforced.

### Enabling and Disabling Defined Integrity Constraints

Use the `ALTER` `TABLE` command to

- enable a disabled constraint, using the ENABLE clause
- disable an enabled constraint, using the DISABLE clause

#### Enabling Disabled Constraints

The following statements are examples of statements that enable disabled integrity constraints:

    ALTER TABLE dept

    ENABLE CONSTRAINT dname_ukey;

    ALTER TABLE dept

    ENABLE PRIMARY KEY,
    ENABLE UNIQUE (dname, loc);

An `ALTER` `TABLE` statement that attempts to enable an integrity constraint fails when the rows of the table violate the integrity constraint. In this case, the statement is rolled back and the constraint is not enabled. Refer to the section "Exception Reporting" on page 9-23 for more information about rows that violate integrity constraints.

#### Disabling Enabled Constraints

The following statements are examples of statements that disable enabled integrity constraints:

    ALTER TABLE dept

    DISABLE CONSTRAINT dname_ukey;

    ALTER TABLE dept

    DISABLE PRIMARY KEY,
    DISABLE UNIQUE (dname, loc);

Tip - Using the Data Dictionary for Reference: The example statements in the previous sections require that you have some information about a constraint to enable or disable it. For example, the first statement of each section requires that you know the constraint's name, while the second statement of each section requires that you know the unique key's column list. If you do not have such information, you can query one of the data dictionary views defined for constraints; for more information about these views, see "Listing Integrity Constraint Definitions" on page 9-27 and Oracle8 Reference.

### Enabling and Disabling Key Integrity Constraints

When enabling or disabling `UNIQUE` key, `PRIMARY` `KEY`, and `FOREIGN` `KEY` integrity constraints, you should be aware of several important issues and prerequisites. For more information about enabling, disabling, and managing FOREIGN KEY constraints. `UNIQUE` key and `PRIMARY` `KEY` constraints are usually managed by the database administrator.

See Also: "Managing FOREIGN KEY Integrity Constraints" on page 9-25., and the Oracle8 Administrator's Guide.

.

### Enabling Constraints after a Parallel Direct Path Load

SQL*Loader permits multiple concurrent sessions to perform a direct path load into the same table. Because each SQL*Loader session can attempt to re-enable constraints on a table after a direct path load, there is a danger that one session may attempt to re-enable a constraint before another session is finished loading data. In this case, the first session to complete the load will be unable to enable the constraint because the remaining sessions possess share locks on the table.

Because there is a danger that some constraints might not be re-enabled after a direct path load, you should check the status of the constraint after completing the load to ensure that it was enabled properly.

#### PRIMARY and UNIQUE KEY constraints

`PRIMARY` `KEY` and `UNIQUE` key constraints create indexes on a table when they are enabled, and subsequently can take a significantly long time to enable after a direct path loading session if the table is very large.

You should consider enabling these constraints manually after a load (and not specify the automatic enable feature). This allows you to manually create the required indexes in parallel to save time before enabling the constraint.

See Also: Oracle8 Tuning

for more information about creating indexes in parallel.

### Exception Reporting

If no exceptions are present when you issue a `CREATE` `TABLE`... `ENABLE`... or `ALTER` `TABLE`... `ENABLE`... statement, the integrity constraint is enabled and all subsequent DML statements are subject to the enabled integrity constraints.

If exceptions exist when you enable a constraint, an error is returned and the integrity constraint remains disabled. When a statement is not successfully executed because integrity constraint exceptions exist, the statement is rolled back. If exceptions exist, you cannot enable the constraint until all exceptions to the constraint are either updated or deleted.

To determine which rows violate the integrity constraint, include the `EXCEPTIONS` option in the `ENABLE` clause of a `CREATE` `TABLE` or `ALTER` `TABLE` statement. The `EXCEPTIONS` option places the `ROWID`, table owner, table name, and constraint name of all exception rows into a specified table. For example, the following statement attempts to enable the primary key of the `DEPT` table; if exceptions exist, information is inserted into a table named `EXCEPTIONS`:

    ALTER TABLE dept ENABLE PRIMARY KEY EXCEPTIONS INTO exceptions;

Create an appropriate exceptions report table to accept information from the `EXCEPTIONS` option of the `ENABLE` clause. Create an exception table by submitting the script UTLEXCPT.SQL. The script creates a tabled named `EXCEPTIONS`. You can create additional exceptions tables with different names by modifying and resubmitting the script.

If duplicate primary key values exist in the `DEPT` table and the name of the `PRIMARY` `KEY` constraint on `DEPT` is `SYS_C00301`, the following rows might be placed in the table `EXCEPTIONS` by the previous statement:

    SELECT * FROM exceptions;

    ROWID                       OWNER   TABLE_NAME       CONSTRAINT
    ------------------          ------  ------------     -----------
    AAAA5bAADAAAAEQAAA          SCOTT   DEPT             SYS_C00301
    AAAA5bAADAAAAEQAAB          SCOTT   DEPT             SYS_C00301

A more informative query would be to join the rows in an exception report table and the master table to list the actual rows that violate a specific constraint. For example:

    SELECT deptno, dname, loc FROM dept, exceptions

    WHERE exceptions.constraint = 'SYS_C00301'
    AND dept.rowid = exceptions.row_id;

    DEPTNO     DNAME          LOC
    ---------- -------------- -------------
    10         ACCOUNTING     NEW YORK
    10         RESEARCH       DALLAS

Rows that violate a constraint must be either updated or deleted from the table that contains the constraint. If updating exceptions, you must change the value that violates the constraint to a value consistent with the constraint or a null (if allowed). After updating or deleting a row in the master table, delete the corresponding rows for the exception in the exception report table to avoid confusion with later exception reports. The statements that update the master table and the exception report table should be in the same transaction to ensure transaction consistency.

For example, to correct the exceptions in the previous examples, the following transaction might be issued:

    UPDATE dept SET deptno = 20 WHERE dname = 'RESEARCH';

    DELETE FROM exceptions WHERE constraint = 'SYS_C00301';

    COMMIT;

When you manage exceptions, your goal should be to eliminate all exceptions in your exception report table. After eliminating all exceptions, you must re-enable the constraint; the constraint is not automatically enabled after the exceptions are handled.

While you are correcting current exceptions for a table with the constraint disabled, other users can issue statements creating new exceptions.

## Altering Integrity Constraints

You cannot alter integrity constraints. If you must alter the action defined by a given integrity constraint, drop the existing constraint and create a replacement.

## Dropping Integrity Constraints

Drop an integrity constraint if the rule that it enforces is no longer true or if the constraint is no longer needed. Drop an integrity constraint using the `ALTER TABLE` command and the `DROP` clause. For example, the following statements drop integrity constraints:

    ALTER TABLE dept

    DROP UNIQUE (dname, loc);

    ALTER TABLE emp

    DROP PRIMARY KEY,
    DROP CONSTRAINT dept_fkey;

    DROP TABLE emp CASCADE CONSTRAINTS;

When dropping `UNIQUE` key, `PRIMARY` `KEY`, and `FOREIGN` `KEY` integrity constraints, you should be aware of several important issues and prerequisites. For more information about dropping `FOREIGN` `KEY` constraints. `UNIQUE` key and `PRIMARY` `KEY` constraints are usually managed by the database administrator.

See Also: "Managing FOREIGN KEY Integrity Constraints" on page 9-25., and the Oracle8 Administrator's Guide.

## Managing FOREIGN KEY Integrity Constraints

General information about defining, enabling, disabling, and dropping all types of integrity constraints is given in the previous sections. The following section supplements this information, focusing specifically on issues regarding FOREIGN KEY integrity constraints.

### Defining FOREIGN KEY Integrity Constraints

The following topics are of interest when defining FOREIGN KEY integrity constraints.

#### Matching of Datatypes

When defining referential integrity constraints, the corresponding column names of the dependent and referenced tables do not need to match. However, they must be of the same datatype.

#### Composite Foreign Keys

Because foreign keys reference primary and unique keys of the parent table, and `PRIMARY` `KEY` and `UNIQUE` key constraints are enforced using indexes, composite foreign keys are limited to 16 columns.

#### Implied Referencing of a Primary Key

If the column list is not included in the `REFERENCES` option when defining a `FOREIGN` KEY constraint (single column or composite), Oracle assumes that you intend to reference the primary key of the specified table. Alternatively, you can explicitly specify the column(s) to reference in the parent table within parentheses. Oracle automatically checks to verify that this column list references a primary or unique key of the parent table. If it does not, an informative error is returned.

#### Privileges Required for FOREIGN KEY Integrity Constraints

To create a FOREIGN KEY constraint, the creator of the constraint must have privileged access to both the parent and the child table.

- The Parent Table The creator of the referential integrity constraint must own the parent table or have `REFERENCES` object privileges on the columns that constitute the parent key of the parent table.
- The Child Table The creator of the referential integrity constraint must have the ability to create tables (that is, the `CREATE` `TABLE` or `CREATE` `ANY` `TABLE` system privilege) or the ability to alter the child table (that is, the `ALTER` object privilege for the child table or the `ALTER` `ANY` `TABLE` system privilege).

In both cases, necessary privileges cannot be obtained via a role; they must be explicitly granted to the creator of the constraint.

These restrictions allow

- the owner of the child table to explicitly decide what constraints are enforced on her or his tables and the other users that can create constraints on her or his tables
- the owner of the parent table to explicitly decide if foreign keys can depend on the primary and unique keys in her tables

#### Specifying Referential Actions for Foreign Keys

Oracle allows two different types of referential integrity actions to be enforced, as specified with the definition of a `FOREIGN` `KEY` constraint:

- The UPDATE/DELETE No Action Restriction This action prevents the update or deletion of a parent key if there is a row in the child table that references the key. By default, all `FOREIGN` `KEY` constraints enforce the no action restriction; no option needs to be specified when defining the constraint to enforce the no action restriction. For example:

      CREATE TABLE emp (
      . . .,
      FOREIGN KEY (deptno) REFERENCES dept);

- The ON DELETE CASCADE Action This action allows referenced data in the parent key to be deleted (but not updated). If referenced data in the parent key is deleted, all rows in the child table that depend on the deleted parent key values are also deleted. To specify this referential action, include the `ON` `DELETE` `CASCADE` option in the definition of the `FOREIGN` `KEY` constraint. For example:

      CREATE TABLE emp ( . . .,
          FOREIGN KEY (deptno) REFERENCES dept
              ON DELETE CASCADE);

### Enabling FOREIGN KEY Integrity Constraints

`FOREIGN` `KEY` integrity constraints cannot be enabled if the referenced primary or unique key's constraint is not present or not enabled.

## Listing Integrity Constraint Definitions

The data dictionary contains the following views that relate to integrity constraints:

- `ALL_CONSTRAINTS`
- `ALL_CONS_COLUMNS`
- `CONSTRAINT_COLUMNS`
- `CONSTRAINT_DEFS`
- `USER_CONSTRAINTS`
- `USER_CONS_COLUMNS`
- `USER_CROSS_REFS`
- `DBA_CONSTRAINTS`
- `DBA_CONS_COLUMNS`
- `DBA_CROSS_REFS`

Refer to Oracle8 Reference for detailed information about each view.

### Examples

Consider the following `CREATE` `TABLE` statements that define a number of integrity constraints, and the following examples:

    CREATE TABLE dept (

    deptno   NUMBER(3) PRIMARY KEY,
    dname    VARCHAR2(15),
    loc      VARCHAR2(15),
    CONSTRAINT dname_ukey UNIQUE (dname, loc),
    CONSTRAINT loc_check1

    CHECK (loc IN ('NEW YORK', 'BOSTON', 'CHICAGO')));

    CREATE TABLE emp (

    empno    NUMBER(5) PRIMARY KEY,
    ename    VARCHAR2(15) NOT NULL,
    job      VARCHAR2(10),
    mgr      NUMBER(5) CONSTRAINT mgr_fkey

     REFERENCES emp ON DELETE CASCADE,

    hiredate DATE,
    sal      NUMBER(7,2),
    comm     NUMBER(5,2),
    deptno   NUMBER(3) NOT NULL
    CONSTRAINT dept_fkey REFERENCES dept);

##### Example 1: Listing All of Your Accessible Constraints

The following query lists all constraints defined on all tables accessible to you, the user:

    SELECT constraint_name, constraint_type, table_name,

    r_constraint_name

    FROM user_constraints;

Considering the example statements at the beginning of this section, a list similar to the one below is returned:

    CONSTRAINT_NAME  C TABLE_NAME  R_CONSTRAINT_NAME
    ---------------  - ----------- ------------------
    SYS_C00275       P DEPT
    DNAME_UKEY       U DEPT
    LOC_CHECK1       C DEPT
    SYS_C00278       C EMP
    SYS_C00279       C EMP
    SYS_C00280       P EMP
    MGR_FKEY         R EMP         SYS_C00280
    DEPT_FKEY        R EMP         SYS_C00275

Notice the following:

- Some constraint names are user specified (such as `DNAME_UKEY`), while others are system specified (such as `SYS_C00275`).
- Each constraint type is denoted with a different character in the `CONSTRAINT_TYPE` column. The table below summarizes the characters used for each constraint type.

  Constraint Type | Character
  `PRIMARY KEY` | P
  `UNIQUE KEY` | U
  `FOREIGN KEY` | R
  `CHECK, NOT NULL` | C

  Note: An additional constraint type is indicated by the character "V" in the `CONSTRAINT_TYPE` column. This constraint type corresponds to constraints created by the `WITH` `CHECK` `OPTION` for views. See Chapter 4, "Managing Schema Objects" for more information about views and the `WITH` `CHECK` `OPTION`.

  ##### Example 2: Distinguishing NOT NULL Constraints from CHECK Constraints

  In the previous example, several constraints are listed with a constraint type of "C". To distinguish which constraints are `NOT` `NULL` constraints and which are `CHECK` constraints in the `EMP` and `DEPT` tables, issue the following query:

      SELECT constraint_name, search_condition

      FROM user_constraints
      WHERE (table_name = 'DEPT' OR table_name = 'EMP') AND

      constraint_type = 'C';

  Considering the example `CREATE` `TABLE` statements at the beginning of this section, a list similar to the one below is returned:

      CONSTRAINT_NAME  SEARCH_CONDITION
      ---------------  ----------------------------------------
      LOC_CHECK1       loc IN ('NEW YORK', 'BOSTON', 'CHICAGO')
      SYS_C00278       ENAME IS NOT NULL
      SYS_C00279       DEPTNO IS NOT NULL

  Notice the following:

  - `NOT` `NULL` constraints are clearly identified in the `SEARCH_CONDITION` column.
  - The conditions for user-defined `CHECK` constraints are explicitly listed in the `SEARCH_CONDITION` column.

  ##### Example 3: Listing Column Names that Constitute an Integrity Constraint

  The following query lists all columns that constitute the constraints defined on all tables accessible to you, the user:

      SELECT constraint_name, table_name, column_name
          FROM user_cons_columns;

  Considering the example statements at the beginning of this section, a list similar to the one below is returned:

      CONSTRAINT_NAME  TABLE_NAME  COLUMN_NAME
      ---------------  ----------- ---------------
      DEPT_FKEY        EMP         DEPTNO
      DNAME_UKEY       DEPT        DNAME
      DNAME_UKEY       DEPT        LOC
      LOC_CHECK1       DEPT        LOC
      MGR_FKEY         EMP         MGR
      SYS_C00275       DEPT        DEPTNO
      SYS_C00278       EMP         ENAME
      SYS_C00279       EMP         DEPTNO
      SYS_C00280       EMP         EMPNO

  Prev | Next
  | Copyright © 1997 Oracle Corporation. All Rights Reserved. |
  Library | Product | Contents | Index