Oracle7 Server Application Developer's Guide

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

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

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