Skip Headers
Oracle® Database Advanced Application Developer's Guide
11g Release 1 (11.1)

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

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

6 Maintaining Data Integrity in Database Applications

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

Topics:

Overview of Constraints

You can define constraints to enforce business rules on data in your tables. Business rules specify conditions and relationships that must always be true, or must always be false. Because each company defines its own policies about things like salaries, employee numbers, inventory tracking, and so on, you can specify a different set of rules for each database table.

When an integrity constraint applies to a table, all data in the table must conform to the corresponding rule. When you issue a SQL statement that modifies data in the table, Oracle Database ensures that the new data satisfies the integrity constraint, without any checking within your program.

Enforcing Business Rules with Constraints

You can enforce rules by defining constraints more reliably than by adding logic to your application. Oracle Database can check that all the data in a table obeys an integrity constraint faster than an application can.

For example, to ensure that each employee works for a valid department:

  1. Create a rule that all values in the department table are unique:

    ALTER TABLE dept_tab
      ADD PRIMARY KEY (deptno);
    
  2. Create a rule that every department listed in the employee table must match one of the values in the department table:

    ALTER TABLE emp_tab
      ADD FOREIGN KEY (deptno)
        REFERENCES dept_tab(deptno);
    

When you add a new employee record to the table, Oracle Database automatically checks that its department number appears in the department table.

To enforce this rule without constraints, you can use a trigger to query the department table and test that each new employee's department is valid. This method is less reliable than using constraints, because SELECT in Oracle Database uses consistent read (CR), so the query might miss uncommitted changes from other transactions.

Enforcing Business Rules with Application Logic

You might enforce business rules through application logic as well as through constraints, if you can filter out bad data before attempting an insert or update. This might let you provide instant feedback to the user, and reduce the load on the database. This technique is appropriate when you can determine that data values are wrong or out of range without checking against any data already in the table.

Creating Indexes for Use with Constraints

All enabled unique and primary keys require corresponding indexes. Create these indexes by hand, rather than letting the database create them. Note that:

  • Constraints use existing indexes where possible, rather than creating new ones.

  • Unique and primary keys can use non-unique as well as unique indexes. They can even use only the first few columns of non-unique indexes.

  • At most one unique or primary key can use each non-unique index.

  • The column orders in the index and the constraint need not match.

  • If you must check whether an index is used by a constraint, for example when you want to drop the index, the object number of the index used by a unique or primary key constraint is stored in CDEF$.ENABLED for that constraint. It is not shown in any static data dictionary view or dynamic performance view.

  • Oracle Database does not automatically index foreign keys.

When to Use NOT NULL Constraints

By default, all columns can contain null values. Define NOT NULL constraints only for columns that always require values. For example, a new employee's manager or hire date might be temporarily omitted. Some employees might not have a commission. Columns like these must not have NOT NULL constraints. However, an employee name might be required from the very beginning, and you can enforce this rule with a NOT NULL integrity constraint.

NOT NULL constraints are often combined with other constraints to further restrict the values that can exist in specific columns. For example, the combination of NOT NULL and UNIQUE constraints forces the input of values in the UNIQUE key, eliminating the possibility that a new row's data conflicts with an existing row's data.

Because Oracle Database indexes do not store keys that are all null, if you want to allow index-only scans of the table or some other operation that requires indexing all rows, you must put a NOT NULL constraint on at least one indexed column.

Specify a NOT NULL constraint like this:

ALTER TABLE table_name MODIFY column_name NOT NULL;

Example 6-1 shows which columns of the EMPLOYEES table have NOT NULL constraints, and what happens if you try to insert NULL values in columns that have NOT NULL constraints.

Example 6-1 Inserting NULL Values into Columns with NOT NULL Constraints

SQL> CONNECT HR
Enter password: password
Connected.
SQL>
SQL> DESCRIBE EMPLOYEES;
Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                               NOT NULL NUMBER(6)
 FIRST_NAME                                         VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
 EMAIL                                     NOT NULL VARCHAR2(25)
 PHONE_NUMBER                                       VARCHAR2(20)
 HIRE_DATE                                 NOT NULL DATE
 JOB_ID                                    NOT NULL VARCHAR2(10)
 SALARY                                             NUMBER(8,2)
 COMMISSION_PCT                                     NUMBER(2,2)
 MANAGER_ID                                         NUMBER(6)
 DEPARTMENT_ID                                      NUMBER(4)

SQL> 
SQL> INSERT INTO EMPLOYEES
  2    VALUES (NULL, 'John', 'Doe', 'johndoe@example.com',
  3            NULL, NULL, NULL, NULL, NULL, NULL, NULL);
  VALUES (NULL, 'John', 'Doe', 'johndoe@example.com',
          *
ERROR at line 2:
ORA-01400: cannot insert NULL into ("HR"."EMPLOYEES"."EMPLOYEE_ID")
 
SQL> 
SQL> INSERT INTO EMPLOYEES
  2    VALUES (123456, 'John', 'Doe', 'johndoe@example.com',
  3            NULL, NULL, NULL, NULL, NULL, NULL, NULL);
          NULL, NULL, NULL, NULL, NULL, NULL, NULL)
                *
ERROR at line 3:
ORA-01400: cannot insert NULL into ("HR"."EMPLOYEES"."HIRE_DATE")
 
SQL>

When to Use Default Column Values

Assign default values to columns that contain typical values. For example, in the DEPT_TAB table, if most departments are located in New York, then the default value for the LOC column can be set to NEW YORK.

Default values can help avoid errors where there is a number, such as zero, that applies to a column that has no entry. For example, a default value of zero can simplify testing, by changing a test like this:

IF sal IS NOT NULL AND sal < 50000

to the simpler form:

IF sal < 50000

Depending upon your business rules, you might use default values to represent zero or false, or leave the default values as NULL to signify an unknown value.

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 through a view. The base table might also have a column named inserter, not included in the definition of the view, to log the user that inserts each row. To record the user name automatically, define a default value that invokes the USER function:

CREATE TABLE audit_trail (
  value1   NUMBER,
  value2   VARCHAR2(32),
  inserter VARCHAR2(30) DEFAULT USER);

Setting Default Column Values

Default values can be defined using any literal, or almost any expression, including calls to the following:

  • SYSDATE

  • SYS_CONTEXT

  • USER

  • USERENV

  • UID

Default values cannot include expressions that refer to a sequence, PL/SQL function, column, LEVEL, ROWNUM, or PRIOR. The data type of a default literal or expression must match or be convertible to the column data type.

Sometimes the default value is the result of a SQL function. For example, a call to SYS_CONTEXT can set a different default value depending on conditions such as the user name. To be used as a default value, a SQL function must have parameters that are all literals, cannot reference any columns, and cannot invoke any other functions.

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

You can use the keyword DEFAULT within an INSERT statement instead of a literal value, and the corresponding default value is inserted.

Choosing a Primary Key for a Table

Each table can have one primary key, which uniquely identifies each row in a table and ensures that no duplicate rows exist. When selecting a primary key, use these guidelines:

  • Whenever practical, use a column containing a sequence number. This satisfies all the other guidelines.

  • Choose a column whose data values are unique, because the purpose of a primary key is to uniquely identify each row of the table.

  • Choose a column whose data values never change. A primary key value is only used to identify a row in the table, and its data must never be used for any other purpose.

  • Choose a column that does not contain any nulls. A PRIMARY KEY constraint, by definition, does not allow any row to contain 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.

  • Minimize your use of composite primary keys. Although composite primary keys are allowed, they do not satisfy all of the other recommendations. For example, composite primary key values are long and cannot be assigned by sequence numbers.

When to Use UNIQUE Constraints

Choose columns for unique keys carefully. The purpose of these constraints is different from that of primary keys. Unique key constraints are appropriate for any column where duplicate values are not allowed. Primary keys identify each row of the table uniquely, and typically contain values that have no significance other than being unique. Figure 6-1 shows an example of a table with a unique key constraint.

Figure 6-1 Table with a UNIQUE Constraint

Table with a UNIQUE Constraint
Description of "Figure 6-1 Table with a UNIQUE Constraint"

Note:

You cannot have identical values in the non-null columns of a composite UNIQUE key constraint (UNIQUE key constraints allow NULL values).

Some examples of good unique keys include:

  • An employee social security number (the primary key might be the employee number)

  • A truck license plate number (the primary key might be the truck number)

  • A customer phone number, consisting of the two columns AREA_CODE and LOCAL_PHONE (the primary key might be the customer number)

  • A department name and location (the primary key might be the department number)

When to Use Constraints On Views

The constraints in this chapter apply to tables, not views.

Although you can declare constraints on views, such constraints do not help maintain data integrity. Instead, they are used to enable query rewrites on queries involving views, which helps performance with materialized views and other data warehousing features. Such constraints are always declared with the DISABLE keyword, and you cannot use the VALIDATE keyword. The constraints are never enforced, and there is no associated index.

See Also:

Oracle Database Data Warehousing Guide for information about using constraints in data warehousing

Enforcing Referential Integrity with Constraints

Whenever two tables contain one or more common columns, Oracle Database can enforce the relationship between the two tables through a referential integrity constraint. Define a PRIMARY or UNIQUE key constraint on the column in the parent table (the one that has the complete set of column values). Define a FOREIGN KEY constraint on the column in the child table (the one whose values must refer to existing values in the other table).

See Also:

Defining Relationships Between Parent and Child Tables for information about defining additional constraints, including the foreign key

Figure 6-2 shows a foreign key defined on the department number. It guarantees that every value in this column must match a value in the primary key of the department table. This constraint prevents erroneous department numbers from getting into the employee table.

Foreign keys can be comprised of multiple columns. Such a composite foreign key must reference a composite primary or unique key of the exact same structure, with the same number of columns and the same data types. Because composite primary and unique keys are limited to 32 columns, a composite foreign key is also limited to 32 columns.

Figure 6-2 Tables with FOREIGN KEY Constraints

Tables with FOREIGN KEY Constraints
Description of "Figure 6-2 Tables with FOREIGN KEY Constraints"

FOREIGN KEY Constraints and NULL Values

Foreign keys allow key values that are all NULL, even if there are no matching PRIMARY or UNIQUE keys.

  • By default (without any NOT NULL or CHECK clauses), the FOREIGN KEY constraint enforces the match none rule for composite foreign keys in the ANSI/ISO standard.

  • To enforce the match full rule for NULL values 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. For example, with 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 NULL values 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 Oracle Database PL/SQL Language Reference.

Defining Relationships Between Parent and Child Tables

Several relationships between parent and child tables can be determined by the other types of 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 6-2 between the employee and department tables. 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.

Any number of rows in the child table can reference the same parent key value, so 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, only one row in the child table can reference a given 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 employee table had a column named MEMBERNO, referring to an employee membership number in the company 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 insurance policy. The MEMBERNO in the employee table must be both a foreign key and a unique key:

  • To enforce referential integrity rules between the EMP_TAB 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 given parent key value, and because NULL values 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 employee 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 employee table.

Rules for Multiple FOREIGN KEY Constraints

Oracle Database allows a column to be referenced by multiple FOREIGN KEY constraints; 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.

Deferring Constraint Checks

When Oracle Database checks a constraint, it signals an error if the constraint is not satisfied. To defer checking constraints until the end of the current transaction, use the SET CONSTRAINTS statement.

Note:

You cannot use the SET CONSTRAINTS statement inside a trigger.

When deferring constraint checks, do the following:

  • Select appropriate data.

    You might want to defer constraint checks on UNIQUE and FOREIGN keys if the data you are working with has any of the following characteristics:

    • Tables are snapshots.

    • Some tables contain a large amount of data being manipulated by another application, which might not return the data in the same order.

  • Update cascade operations on foreign keys.

  • Ensure that constraints are deferrable.

    After identifying the appropriate tables, ensure that their FOREIGN, UNIQUE and PRIMARY key constraints are created DEFERRABLE.

  • Within the application that manipulates the data, set all constraints deferred before you begin processing any data, as follows:

    SET CONSTRAINTS ALL DEFERRED;
    
  • (Optional) Check for constraint violations immediately before committing the transaction.

    Immediately before the COMMIT statement, execute the SET CONSTRAINTS ALL IMMEDIATE statement. If there are any problems with a constraint, this statement fails, and identifies the constraint that caused the error. If you commit while constraints are violated, the transaction rolls back and you get an error message.

In Example 6-2, the PRIMARY and FOREIGN keys of the table emp are created DEFERRABLE and then deferred.

Example 6-2 Deferring Constraint Checks

SQL> -- Create dept table:
SQL> 
SQL> DROP TABLE dept;
DROP TABLE dept
           *
ERROR at line 1:
ORA-00942: table or view does not exist
 
SQL> 
SQL> CREATE TABLE dept (
  2    deptno NUMBER PRIMARY KEY,
  3    dname  VARCHAR2 (30)
  4  );
 
Table created.
 
SQL> 
SQL> -- Create emp table with deferrable primary & foreign constraints:
SQL> 
SQL> DROP TABLE emp;
DROP TABLE emp
           *
ERROR at line 1:
ORA-00942: table or view does not exist
 
SQL> 
SQL> CREATE TABLE emp (
  2    empno  NUMBER,
  3    ename  VARCHAR2(30),
  4    deptno NUMBER,
  5    CONSTRAINT pk_emp_empno PRIMARY KEY (empno) DEFERRABLE,
  6    CONSTRAINT fk_emp_deptno FOREIGN KEY (deptno) REFERENCES dept(deptno) DEFERRABLE
  7  );
 
Table created.
 
SQL> 
SQL> -- Populate tables:
SQL> 
SQL> INSERT INTO dept VALUES (10, 'Accounting');
 
1 row created.
 
SQL> INSERT INTO dept VALUES (20, 'SALES');
 
1 row created.
 
SQL> INSERT INTO emp VALUES (1, 'Corleone', 10);
 
1 row created.
 
SQL> INSERT INTO emp VALUES (2, 'Costanza', 20);
 
1 row created.
 
SQL> COMMIT;
 
Commit complete.
 
SQL> 
SQL> -- Defer constraint checking & update tables:
SQL> 
SQL> SET CONSTRAINTS ALL DEFERRED;
 
Constraint set.
 
SQL> 
SQL> UPDATE dept
  2    SET deptno = deptno + 10
  3      WHERE deptno = 20;
 
1 row updated.
 
SQL> 
SQL> SELECT * from dept
  2    ORDER BY deptno;
 
    DEPTNO DNAME
---------- ------------------------------
        10 Accounting
        30 SALES
 
2 rows selected.
 
SQL> 
SQL> UPDATE emp
  2    SET deptno = deptno + 10
  3      WHERE deptno = 20;
 
1 row updated.
 
SQL> 
SQL> SELECT * from emp
  2    ORDER BY deptno;
 
     EMPNO ENAME                              DEPTNO
---------- ------------------------------ ----------
         1 Corleone                               10
         2 Costanza                               30
 
2 rows selected.
 
SQL>

The SET CONSTRAINTS applies only to the current transaction, and its setting lasts for the duration of the transaction, or until another SET CONSTRAINTS statement resets the mode. The ALTER SESSION SET CONSTRAINTS statement applies only for the current session. The defaults specified when you create a constraint remain as long as the constraint exists.

See Also:

Oracle Database SQL Language Reference for more information about the SET CONSTRAINTS statement

Minimizing Space and Time Overhead for Indexes Associated with Constraints

When you create a UNIQUE or PRIMARY key, Oracle Database checks to see if an existing index can be used to enforce uniqueness for the constraint. If there is no such index, the database creates one.

When Oracle Database uses a unique index to enforce a constraint, and constraints associated with the unique index are dropped or disabled, the index is dropped. To preserve the statistics associated with the index (which would take a long time to re-create), specify the KEEP INDEX clause on the DROP statement for the constraint.

While enabled foreign keys reference a PRIMARY or UNIQUE key, you cannot disable or drop the PRIMARY or UNIQUE key constraint or the index.

Note:

UNIQUE and PRIMARY keys with deferrable constraints must all use non-unique indexes.

To use existing indexes when creating unique and primary key constraints, include USING INDEX in the CONSTRAINT clause. For details and examples, see Oracle Database SQL Language Reference.

Guidelines for Indexing Foreign Keys

Index foreign keys unless the matching unique or primary key is never updated or deleted.

See Also:

Oracle Database Concepts for more information about indexing foreign keys

Referential Integrity in a Distributed Database

The declaration of a referential constraint cannot specify a foreign key that references a primary or unique key of a remote table.

However, you can maintain parent/child table relationships across nodes using triggers.

See Also:

Oracle Database PL/SQL Language Reference for more information about triggers that enforce referential integrity

Note:

If you decide to define referential integrity across the nodes of a distributed database using triggers, be aware that network failures can make both the parent table and the child table inaccessible.

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, then some DML statements against the child table (those that insert rows or update a foreign key value) cannot proceed, because the referential integrity triggers must have access to the parent table in the HQ database.

When to Use CHECK Constraints

Use CHECK constraints when you must enforce integrity rules based on logical expressions, such as comparisons. Never use CHECK constraints when any of the other types of constraints can provide the necessary checking.

Examples of CHECK constraints include the following:

Restrictions on CHECK Constraints

A CHECK 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, then 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 or ROWNUM.

  • The condition cannot contain the PRIOR operator.

  • The condition cannot contain a user-defined function.

See Also:

Designing CHECK Constraints

When using CHECK constraints, remember that a CHECK constraint is violated only if the condition evaluates to false; true and unknown values (such as comparisons with nulls) do not violate a check condition. Ensure that any CHECK constraint that you define is specific enough to enforce the rule.

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 employee table unless the employee salary is greater than zero or the employee commission is greater than or equal to zero." But if a row is inserted with a null salary, that row does not violate the CHECK constraint, regardless of whether or not the commission value is valid, because the entire check condition is evaluated as unknown. In this case, you can prevent such violations by placing NOT NULL 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 conditions in Oracle Database SQL Language Reference

Rules for 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.

The order in which the constraints are evaluated is not defined, so be careful not to rely on the order or to define multiple constraints that conflict with each other.

Choosing Between CHECK and NOT NULL Constraints

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

CHECK (column_name IS NOT NULL)

Therefore, you can write NOT NULL constraints for a single column using either a NOT NULL constraint or a CHECK constraint. The NOT NULL constraint is easier to use than the CHECK constraint.

In the case where a composite key can allow only all nulls or all values, you must use a CHECK constraint. For example, the following CHECK 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))

Examples of Defining Constraints

Example 6-3 and Example 6-4 show how to create simple constraints during the prototype phase of your database design. In these examples, each constraint is given a name. Naming the constraints prevents the database from creating multiple copies of the same constraint, with different system-generated names, if the DDL is run multiple times.

Example 6-3 creates tables and their constraints at the same time, using the CREATE TABLE statement.

Example 6-3 Defining Constraints with the CREATE TABLE Statement

SQL> DROP TABLE DeptTab;
Table dropped.
 
SQL> 
SQL> CREATE TABLE DeptTab (
  2    Deptno  NUMBER(3) CONSTRAINT pk_DeptTab_Deptno PRIMARY KEY,
  3    Dname   VARCHAR2(15),
  4    Loc     VARCHAR2(15),
  5    CONSTRAINT u_DeptTab_Dname_Loc UNIQUE (Dname, Loc),
  6    CONSTRAINT c_DeptTab_Loc
  7      CHECK (Loc IN ('NEW YORK', 'BOSTON', 'CHICAGO')));
 
Table created.
 
SQL> 
SQL> DROP TABLE EmpTab;
DROP TABLE EmpTab
           *
ERROR at line 1:
ORA-00942: table or view does not exist
 
SQL> 
SQL> CREATE TABLE EmpTab (
  2    Empno    NUMBER(5) CONSTRAINT pk_EmpTab_Empno PRIMARY KEY,
  3    Ename    VARCHAR2(15) NOT NULL,
  4    Job      VARCHAR2(10),
  5    Mgr      NUMBER(5) CONSTRAINT r_EmpTab_Mgr REFERENCES EmpTab,
  6    Hiredate DATE,
  7    Sal      NUMBER(7,2),
  8    Comm     NUMBER(5,2),
  9    Deptno   NUMBER(3) NOT NULL
 10    CONSTRAINT r_EmpTab_DeptTab REFERENCES DeptTab ON DELETE CASCADE);
 
Table created.
 
SQL> 

Example 6-4 creates constraints for existing tables, using the ALTER TABLE statement.

You cannot create a validated constraint on a table if the table already contains rows that violate the constraint.

Example 6-4 Defining Constraints with the ALTER TABLE Statement

SQL> -- Create tables without constraints:
SQL> 
SQL> DROP TABLE DeptTab;
 
Table dropped.
 
SQL> 
SQL> CREATE TABLE DeptTab (
  2    Deptno  NUMBER(3),
  3    Dname   VARCHAR2(15),
  4    Loc     VARCHAR2(15)
  5  );
 
Table created.
 
SQL> 
SQL> DROP TABLE EmpTab;
DROP TABLE EmpTab
           *
ERROR at line 1:
ORA-00942: table or view does not exist
 
 
SQL> 
SQL> CREATE TABLE EmpTab (
  2    Empno    NUMBER(5),
  3    Ename    VARCHAR2(15),
  4    Job      VARCHAR2(10),
  5    Mgr      NUMBER(5),
  6    Hiredate DATE,
  7    Sal      NUMBER(7,2),
  8    Comm     NUMBER(5,2),
  9    Deptno   NUMBER(3)
 10  );
 
Table created.
 
SQL> 
SQL> --Define constraints with the ALTER TABLE statement:
SQL> 
SQL> ALTER TABLE DeptTab
  2    ADD CONSTRAINT pk_DeptTab_Deptno PRIMARY KEY (Deptno);
 
Table altered.
 
SQL> 
SQL> ALTER TABLE EmpTab
  2    ADD CONSTRAINT fk_DeptTab_Deptno
  3      FOREIGN KEY (Deptno) REFERENCES DeptTab;
 
Table altered.
 
SQL> 
SQL> ALTER TABLE EmpTab MODIFY (Ename VARCHAR2(15) NOT NULL);
 
Table altered.
 
SQL>

See Also:

Oracle Database Administrator's Guide for information about creating and maintaining constraints for a large production database

Privileges Needed to Define Constraints

The creator of a constraint must have the ability to create tables (the CREATE TABLE or CREATE ANY TABLE system privilege), or the ability to alter the table (the ALTER object privilege for the table or the ALTER ANY TABLE system privilege) with the constraint. Additionally, UNIQUE and PRIMARY KEY 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 constraints also require some additional privileges.

Naming Constraints

Assign names to constraints NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK 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 automatically by Oracle Database.

Choosing your own name makes error messages for constraint violations more understandable, and prevents the creation of duplicate constraints with different names if the SQL statements are run more than once.

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

See Also:

Viewing Information About Constraints for examples of static data dictionary views

Enabling and Disabling Constraints

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

enabled constraint. When a constraint is enabled, the corresponding rule is enforced on the data values in the associated columns. The definition of the constraint is stored in the data dictionary.

disabled constraint. When a constraint is disabled, the corresponding rule is not enforced. The definition of the constraint is still stored in the data dictionary.

An integrity constraint represents an assertion about the data in a database. This assertion is always true when the constraint is enabled. The assertion may or may not be true when the constraint is disabled, because data that violates the integrity constraint can be in the database.

Topics:

Why Disable Constraints?

During day-to-day operations, keep constraints enabled. In certain situations, temporarily disabling the constraints of a table makes sense 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 each employee number by adding 1000 to the existing number)

  • When importing or exporting one table at a time

Temporarily turning off constraints can speed up these operations.

Creating Enabled Constraints (Default)

When you define an integrity constraint (using either CREATE TABLE or ALTER TABLE), Oracle Database enables the constraint by default. For code clarity, you can explicitly enable the constraint by including the ENABLE clause in its definition, as in Example 6-5.

Example 6-5 Creating Enabled Constraints

SQL> /* Use CREATE TABLE statement to create enabled constraint
SQL>    (ENABLE keyword is optional): */
SQL> 
SQL> DROP TABLE t1;
DROP TABLE t1
           *
ERROR at line 1:
ORA-00942: table or view does not exist
 
SQL> 
SQL> CREATE TABLE t1
  2    (Empno NUMBER(5) PRIMARY KEY ENABLE);
 
Table created.
 
SQL> 
SQL> /* Create table without constraint
SQL>    & then use ALTER TABLE statement to add enabled constraint
SQL>    (ENABLE keyword is optional): */
SQL> 
SQL> DROP TABLE t2;
DROP TABLE t2
           *
ERROR at line 1:
ORA-00942: table or view does not exist
 
SQL> 
SQL> CREATE TABLE t2 (
  2    Empno NUMBER(5));
 
Table created.
 
SQL> 
SQL> ALTER TABLE t2
  2    ADD PRIMARY KEY (Empno) ENABLE;
 
Table altered.
 
SQL> 

Include the ENABLE clause when defining a constraint for a table that will be populated a row at a time by individual transactions. This ensures that data is consistent at all times, and reduces the performance overhead of each DML operation.

An ALTER TABLE statement that tries to enable an integrity constraint fails if an existing row of the table violates the integrity constraint. The statement rolls back and the constraint definition is neither stored nor enabled.

See Also:

Fixing Constraint Exceptions for more information about rows that violate constraints

Creating Disabled Constraints

You define and disable an integrity constraint (using either CREATE TABLE or ALTER TABLE), by including the DISABLE clause in its definition, as in Example 6-6.

Example 6-6 Creating Disabled Constraints

SQL> /* Use CREATE TABLE statement to create disabled constraint */
SQL> 
SQL> DROP TABLE t1;
DROP TABLE t1
           *
ERROR at line 1:
ORA-00942: table or view does not exist
 
SQL> 
SQL> CREATE TABLE t1
  2    (Empno NUMBER(5) PRIMARY KEY DISABLE);
 
Table created.
 
SQL> 
SQL> /* Create table without constraint
SQL>    & then use ALTER TABLE statement to add disabled constraint */
SQL> 
SQL> DROP TABLE t2;
DROP TABLE t2
           *
ERROR at line 1:
ORA-00942: table or view does not exist
 
SQL> 
SQL> CREATE TABLE t2 (
  2    Empno NUMBER(5));
 
Table created.
 
SQL> 
SQL> ALTER TABLE t2
  2    ADD PRIMARY KEY (Empno) DISABLE;
 
Table altered.
 
SQL> 

Include the DISABLE clause when defining a constraint for a table that will have large amounts of data inserted before anybody else accesses it, particularly if you must cleanse data after inserting it, or must fill empty columns with sequence numbers or parent/child relationships.

An ALTER TABLE statement that defines and disables a constraint never fails, because its rule is not enforced.

Enabling Existing Constraints

After you have cleansed the data and filled the empty columns, you can enable constraints that were disabled during data insertion.

To enable an existing constraint, use the ALTER TABLE statement with the ENABLE clause, as in Example 6-7.

Example 6-7 Enabling Existing Constraints

SQL> -- Create table with disabled constraints:
SQL> 
SQL> DROP TABLE DeptTab;
DROP TABLE DeptTab
           *
ERROR at line 1:
ORA-00942: table or view does not exist
 
SQL> 
SQL> CREATE TABLE DeptTab (
  2     Deptno  NUMBER(3) PRIMARY KEY DISABLE,
  3     Dname   VARCHAR2(15),
  4     Loc     VARCHAR2(15),
  5     CONSTRAINT uk_DeptTab_Dname_Loc UNIQUE (Dname, Loc) DISABLE,
  6     CONSTRAINT c_DeptTab_Loc
  7       CHECK (Loc IN ('NEW YORK', 'BOSTON', 'CHICAGO')) DISABLE
  8  );
 
Table created.
 
SQL> 
SQL> -- Enable constraints:
SQL> 
SQL> ALTER TABLE DeptTab
  2    ENABLE PRIMARY KEY
  3    ENABLE CONSTRAINT uk_DeptTab_Dname_Loc
  4    ENABLE CONSTRAINT c_DeptTab_Loc;
 
Table altered.
 
SQL> 

An ALTER TABLE statement that attempts to enable an integrity constraint fails if any of the table rows violate the integrity constraint. The statement is rolled back and the constraint is not enabled.

See Also:

Fixing Constraint Exceptions for more information about rows that violate constraints

Disabling Existing Constraints

If you must perform a large insert or update when a table already contains data, you can temporarily disable constraints to improve performance of the bulk operation.

To disable an existing constraint, use the ALTER TABLE statement with the DISABLE clause, as in Example 6-8.

Example 6-8 Disabling Existing Constraints

SQL> -- Create table with enabled constraints:
SQL> 
SQL> DROP TABLE DeptTab;
DROP TABLE DeptTab
           *
ERROR at line 1:
ORA-00942: table or view does not exist
 
SQL> 
SQL> CREATE TABLE DeptTab (
  2     Deptno  NUMBER(3) PRIMARY KEY ENABLE,
  3     Dname   VARCHAR2(15),
  4     Loc     VARCHAR2(15),
  5     CONSTRAINT uk_DeptTab_Dname_Loc UNIQUE (Dname, Loc) ENABLE,
  6     CONSTRAINT c_DeptTab_Loc
  7       CHECK (Loc IN ('NEW YORK', 'BOSTON', 'CHICAGO')) ENABLE
  8  );
 
Table created.
 
SQL> 
SQL> -- Disable constraints:
SQL> 
SQL> ALTER TABLE DeptTab
  2    DISABLE PRIMARY KEY
  3    DISABLE CONSTRAINT uk_DeptTab_Dname_Loc
  4    DISABLE CONSTRAINT c_DeptTab_Loc;
 
Table altered.
 
SQL>

Guidelines for Enabling and Disabling Key Constraints

When enabling or disabling UNIQUE, PRIMARY KEY, and FOREIGN KEY constraints, be aware of several important issues and prerequisites. UNIQUE key and PRIMARY KEY constraints are usually managed by the database administrator.

Fixing Constraint Exceptions

If a row of a table disobeys an integrity constraint, then this row is in violation of the constraint and is called an exception to the constraint. If any exceptions exist, then the constraint cannot be enabled. The rows that violate the constraint must be updated or deleted before the constraint can be enabled.

You can identify exceptions for a specific integrity constraint as you try to enable the constraint.

See Also:

Fixing Constraint Exceptions for more information about this procedure

When you try to create or enable a constraint, and the statement fails because integrity constraint exceptions exist, the statement is rolled back. You cannot enable the constraint until all exceptions 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.

See Also:

Oracle Database Administrator's Guide for more information about responding to constraint exceptions

Modifying Constraints

Starting with Oracle8i, you can modify an existing constraint with the MODIFY CONSTRAINT clause, as in Example 6-9.

See Also:

Oracle Database SQL Language Reference for information about the parameters you can modify

Example 6-9 Modifying Constraints

SQL> /* Create & then modify a CHECK constraint: */
SQL> 
SQL> DROP TABLE X1Tab;
DROP TABLE X1Tab
           *
ERROR at line 1:
ORA-00942: table or view does not exist
 
SQL> 
SQL> CREATE TABLE X1Tab (
  2    a1 NUMBER
  3      CONSTRAINT c_X1Tab_a1 CHECK (a1>3)
  4        DEFERRABLE DISABLE
  5  );
 
Table created.
 
SQL> 
SQL> ALTER TABLE X1Tab
  2    MODIFY CONSTRAINT c_X1Tab_a1 ENABLE;
 
Table altered.
 
SQL> 
SQL> ALTER TABLE X1Tab
  2    MODIFY CONSTRAINT c_X1Tab_a1 RELY;
 
Table altered.
 
SQL> 
SQL> ALTER TABLE X1Tab
  2    MODIFY CONSTRAINT c_X1Tab_a1 INITIALLY DEFERRED;
 
Table altered.
 
SQL> 
SQL> ALTER TABLE X1Tab
  2    MODIFY CONSTRAINT c_X1Tab_a1 ENABLE NOVALIDATE;
 
Table altered.
 
SQL> 
SQL> /* Create & then modify a PRIMARY KEY constraint: */
SQL> 
SQL> DROP TABLE t1;
DROP TABLE t1
           *
ERROR at line 1:
ORA-00942: table or view does not exist
 
SQL> 
SQL> CREATE TABLE t1 (a1 INT, b1 INT);
 
Table created.
 
SQL> 
SQL> ALTER TABLE t1
  2    ADD CONSTRAINT pk_t1_a1 PRIMARY KEY(a1) DISABLE;
 
Table altered.
 
SQL> 
SQL> ALTER TABLE t1
  2    MODIFY PRIMARY KEY INITIALLY IMMEDIATE
  3      USING INDEX PCTFREE = 30 ENABLE NOVALIDATE;
 
Table altered.
 
SQL> 
SQL> ALTER TABLE t1
  2    MODIFY PRIMARY KEY ENABLE NOVALIDATE;
 
Table altered.
 
SQL> 

Renaming Constraints

One of the properties of a constraint that you can modify is its name. Situations in which you would rename a constraint include the following:

  • You want to clone a table and its constraints.

    Constraint names must be unique, even across multiple schemas. Therefore, the constraints in the original table cannot have the same names as those in the cloned table.

  • You created a constraint with a default system-generated name, and now you want to give it a name that is easy to remember, so that you can easily enable and disable it.

Example 6-10 shows how to find the system-generated name of a constraint and change it.

Example 6-10 Renaming a Constraint

SQL> DROP TABLE T;
Table dropped.
 
SQL> CREATE TABLE T (
  2  C1 NUMBER PRIMARY KEY,
  3  C2 NUMBER);
  4  );
 
Table created.
 
SQL> SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS
  2  WHERE TABLE_NAME = 'T'
  3  AND CONSTRAINT_TYPE = 'P';
 
CONSTRAINT_NAME
------------------------------
SYS_C006075
 
1 row selected.
 
SQL> ALTER TABLE T
  2  RENAME CONSTRAINT SYS_C006075
  3  TO T_C1_PK;
 
Table altered.
 
SQL> SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS
  2  WHERE TABLE_NAME = 'T'
  3  AND CONSTRAINT_TYPE = 'P';
 
CONSTRAINT_NAME
------------------------------
T_C1_PK
 
1 row selected.
 
SQL>

Dropping Constraints

You can drop a constraint using the DROP clause of the ALTER TABLE statement. Situations in which you would drop a constraint include the following:

To drop a constraint and all other integrity constraints that depend on it, specify CASCADE.

Example 6-11 Dropping Constraints

SQL> -- Create table with constraints:
SQL> 
SQL> DROP TABLE DeptTab;
DROP TABLE DeptTab
           *
ERROR at line 1:
ORA-00942: table or view does not exist
 
SQL> CREATE TABLE DeptTab (
  2    Deptno  NUMBER(3) PRIMARY KEY,
  3    Dname   VARCHAR2(15),
  4    Loc     VARCHAR2(15),
  5    CONSTRAINT uk_DeptTab_Dname_Loc UNIQUE (Dname, Loc),
  6    CONSTRAINT c_DeptTab_Loc
  7      CHECK (Loc IN ('NEW YORK', 'BOSTON', 'CHICAGO'))
  8  );
 
Table created.
 
SQL> -- Drop constraints:
SQL> 
SQL> ALTER TABLE DeptTab
  2    DROP PRIMARY KEY
  3    DROP CONSTRAINT uk_DeptTab_Dname_Loc
  4    DROP CONSTRAINT c_DeptTab_Loc;
 
Table altered.
 
SQL> 

When dropping UNIQUE, PRIMARY KEY, and FOREIGN KEY constraints, be aware of several important issues and prerequisites. UNIQUE and PRIMARY KEY constraints are usually managed by the database administrator.

See Also:

Managing FOREIGN KEY Constraints

FOREIGN KEY constraints enforce relationships between columns in different tables. Therefore, they cannot be enabled if the constraint of the referenced primary or unique key is not present or not enabled.

Data Types and Names for Foreign Key Columns

You must use the same data type for corresponding columns in the dependent and referenced tables. The column names need not match.

Limit on Columns in 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 32 columns.

Foreign Key References Primary Key by Default

If the column list is not included in the REFERENCES option when defining a FOREIGN KEY constraint (single column or composite), then Oracle Database 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 Database automatically checks to verify that this column list references a primary or unique key of the parent table. If it does not, then an informative error is returned.

Privileges Required to Create FOREIGN KEY Constraints

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

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

  • 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 through 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 which constraints are enforced and which other users can create constraints

  • The owner of the parent table to explicitly decide if foreign keys can depend on the primary and unique keys in her tables

Choosing How Foreign Keys Enforce Referential Integrity

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

  • Prevent Delete or Update of Parent Key The default setting prevents the deletion or update of a parent key if there is a row in the child table that references the key. For example:

    CREATE TABLE Emp_tab (
      FOREIGN KEY (Deptno) REFERENCES Dept_tab);
     
    
  • Delete Child Rows When Parent Key Deleted The ON DELETE CASCADE action allows parent key data that is referenced from the child table to be deleted, but not updated. When 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_tab (
      FOREIGN KEY (Deptno) REFERENCES Dept_tab
        ON DELETE CASCADE); 
    
  • Set Foreign Keys to Null When Parent Key Deleted The ON DELETE SET NULL action allows data that references the parent key to be deleted, but not updated. When referenced data in the parent key is deleted, all rows in the child table that depend on those parent key values have their foreign keys set to null. To specify this referential action, include the ON DELETE SET NULL option in the definition of the FOREIGN KEY constraint. For example:

    CREATE TABLE Emp_tab ( 
        FOREIGN KEY (Deptno) REFERENCES Dept_tab  
            ON DELETE SET NULL); 
    

Viewing Information About Constraints

To find the names of constraints, what columns they affect, and other information to help you manage them, query the static data dictionary views *_CONSTRAINTS and *_CONS_COLUMNS, as in Example 6-12.

See Also:

Oracle Database Reference for information about *_CONSTRAINTS and *_CONS_COLUMNS

Example 6-12 Viewing Information About Constraints

SQL> DROP TABLE DeptTab;
DROP TABLE DeptTab
           *
ERROR at line 1:
ORA-00942: table or view does not exist
 
SQL> CREATE TABLE DeptTab (
  2    Deptno  NUMBER(3) PRIMARY KEY,
  3    Dname   VARCHAR2(15),
  4    Loc     VARCHAR2(15),
  5    CONSTRAINT uk_DeptTab_Dname_Loc UNIQUE (Dname, Loc),
  6    CONSTRAINT c_DeptTab_Loc
  7      CHECK (Loc IN ('NEW YORK', 'BOSTON', 'CHICAGO'))
  8  );
 
Table created.
 
SQL> DROP TABLE EmpTab;
DROP TABLE EmpTab
           *
ERROR at line 1:
ORA-00942: table or view does not exist
 
SQL> CREATE TABLE EmpTab (
  2    Empno    NUMBER(5) PRIMARY KEY,
  3    Ename    VARCHAR2(15) NOT NULL,
  4    Job      VARCHAR2(10),
  5    Mgr      NUMBER(5) CONSTRAINT r_EmpTab_Mgr
  6               REFERENCES EmpTab ON DELETE CASCADE,
  7    Hiredate DATE,
  8    Sal      NUMBER(7,2),
  9    Comm     NUMBER(5,2),
 10    Deptno   NUMBER(3) NOT NULL
 11      CONSTRAINT r_EmpTab_Deptno REFERENCES DeptTab
 12  );
 
Table created.
 
SQL> 
SQL> -- Format columns (optional):
SQL> 
SQL> COLUMN CONSTRAINT_NAME   FORMAT A20;
SQL> COLUMN CONSTRAINT_TYPE   FORMAT A4 HEADING 'TYPE';
SQL> COLUMN TABLE_NAME        FORMAT A10;
SQL> COLUMN R_CONSTRAINT_NAME FORMAT A17;
SQL> COLUMN SEARCH_CONDITION  FORMAT A40;
SQL> COLUMN COLUMN_NAME       FORMAT A12;
SQL> 
SQL> -- List your accessible constraints in DeptTab and EmpTab:
SQL> 
SQL> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, R_CONSTRAINT_NAME
  2    FROM USER_CONSTRAINTS
  3      WHERE (TABLE_NAME = 'DEPTTAB' OR TABLE_NAME = 'EMPTAB')
  4        ORDER BY CONSTRAINT_NAME;
 
CONSTRAINT_NAME      TYPE TABLE_NAME R_CONSTRAINT_NAME
-------------------- ---- ---------- -----------------
C_DEPTTAB_LOC        C    DEPTTAB
R_EMPTAB_DEPTNO      R    EMPTAB     SYS_C006286
R_EMPTAB_MGR         R    EMPTAB     SYS_C006290
SYS_C006286          P    DEPTTAB
SYS_C006288          C    EMPTAB
SYS_C006289          C    EMPTAB
SYS_C006290          P    EMPTAB
UK_DEPTTAB_DNAME_LOC U    DEPTTAB
 
8 rows selected.
 
SQL> 
SQL> -- Distinguish between NOT NULL and CHECK constraints
SQL> -- in DeptTab and EmpTab:
SQL> 
SQL> SELECT CONSTRAINT_NAME, SEARCH_CONDITION
  2    FROM USER_CONSTRAINTS
  3      WHERE (TABLE_NAME = 'DEPTTAB' OR TABLE_NAME = 'EMPTAB')
  4         AND CONSTRAINT_TYPE = 'C'
  5           ORDER BY CONSTRAINT_NAME;
 
CONSTRAINT_NAME      SEARCH_CONDITION
-------------------- ----------------------------------------
C_DEPTTAB_LOC        Loc IN ('NEW YORK', 'BOSTON', 'CHICAGO')
SYS_C006288          "ENAME" IS NOT NULL
SYS_C006289          "DEPTNO" IS NOT NULL
 
3 rows selected.
 
SQL> 
SQL> -- For DeptTab and EmpTab, list columns that constitute constraints:
SQL> 
SQL> SELECT CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME
  2    FROM USER_CONS_COLUMNS
  3      WHERE (TABLE_NAME = 'DEPTTAB' OR TABLE_NAME = 'EMPTAB')
  4        ORDER BY CONSTRAINT_NAME;
 
CONSTRAINT_NAME      TABLE_NAME COLUMN_NAME
-------------------- ---------- ------------
C_DEPTTAB_LOC        DEPTTAB    LOC
R_EMPTAB_DEPTNO      EMPTAB     DEPTNO
R_EMPTAB_MGR         EMPTAB     MGR
SYS_C006286          DEPTTAB    DEPTNO
SYS_C006288          EMPTAB     ENAME
SYS_C006289          EMPTAB     DEPTNO
SYS_C006290          EMPTAB     EMPNO
UK_DEPTTAB_DNAME_LOC DEPTTAB    LOC
UK_DEPTTAB_DNAME_LOC DEPTTAB    DNAME
 
9 rows selected.
 
SQL>

Notice the following:

Notice that the following are explicitly listed in the SEARCH_CONDITION column: