Oracle7 Server Application Developer's Guide

Library

Product

Contents

Index

Maintaining Data Integrity

• Using Integrity Constraints
• Using Referential Integrity Constraints
• Using CHECK Integrity Constraints
• Defining Integrity Constraints
• Enabling and Disabling Integrity Constraints
• Altering Integrity Constraints
• Dropping Integrity Constraints
• Managing FOREIGN KEY Integrity Constraints

• using integrity constraints

• using referential integrity constraints

• referential integrity in a distributed database

• using CHECK integrity constraints

• defining integrity constraints

• enabling and disabling integrity constraints

• altering and dropping integrity constraints

• managing FOREIGN KEY constraints

• listing constraint definitions

Using Integrity Constraints

When to Use Integrity Constraints

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

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

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 "Relationships Between Parent and Child Tables" .

Figure 6 - 1. NOT NULL Integrity Constraints

Setting Default Column Values

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

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

Figure 6 - 2. A UNIQUE Key Constraint

Choosing a Table's 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

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", .

Figure 6 - 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

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

Relationships Between Parent and Child Tables

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 - 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)

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

Concurrency Control, Indexes, and Foreign Keys

No Index on the Foreign Key Figure 6 - 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 6 - 4. Locking mechanisms Used When No Index Is Defined on the Foreign Key

Index on the Foreign Key Figure 6 - 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 6 - 5. Locking mechanisms Used 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

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

• 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

• 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 the Oracle7 Server SQL Reference manual for an explanation of these pseudocolumns.

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

Designing CHECK Constraints

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 the Oracle7 Server SQL Reference manual.

Multiple CHECK Constraints

CHECK and NOT NULL Integrity Constraints

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

There are additional considerations if you are using Trusted Oracle; see the Trusted Oracle7 Server Administrator's Guide for more information.

The CREATE TABLE Command

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

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

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

Table 6 - 1. Restrictions for Defining Integrity Constraints with the ALTER TABLE Command

* 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

Naming Integrity Constraints

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. Refer to the section "Listing Integrity Constraint Definitions" for examples of data dictionary views.

Enabling and Disabling Constraints Upon Definition

UNIQUE Key, PRIMARY KEY, and FOREIGN KEY

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?

• 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

Integrity Constraint Violations

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 Definition

Enabling 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" for more information about rows that violate integrity constraints.

Disabling 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

• enable a disabled constraint, using the ENABLE clause

• disable an enabled constraint, using the DISABLE clause

Enabling Disabled 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" for more information about rows that violate integrity constraints.

Disabling Enabled 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

Enabling and Disabling Key Integrity Constraints

Enabling Constraints after a Parallel Direct Path Load

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

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 the Oracle7 Server Tuning manual for more information about creating indexes in parallel.

Exception Reporting

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  
------------------      ------ ------------  -----------  
000003A5.000C.0001     SCOTT   DEPT      SYS_C00301  
000003A5.000D.0001     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

Dropping 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, see "Managing FOREIGN KEY Integrity Constraints" . UNIQUE key and PRIMARY KEY constraints are usually managed by the database administrator; see the Oracle7 Server Administrator's Guide for more information.

Managing FOREIGN KEY Integrity Constraints

Defining FOREIGN KEY Integrity Constraints

Matching of Datatypes

Composite Foreign Keys

Implied Referencing of a Primary Key

Privileges Required for FOREIGN KEY Integrity Constraints

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

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

• 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

Listing Integrity Constraint Definitions

• 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

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.

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.

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 © 1996 Oracle Corporation. All Rights Reserved.

Library

Product

Contents

Index