5 Data Integrity

Techniques for Guaranteeing Data Integrity

When designing a database application, developers have various options for guaranteeing the integrity of data stored in the database. These options include:

• Enforcing business rules with triggered stored database procedures, as described in "Overview of Triggers"

• Using stored procedures to completely control access to data, as described in "Introduction to Server-Side Programming"

• Enforcing business rules in the code of a database application

• Using Oracle Database integrity constraints, which are rules defined at the column or object level that restrict values in the database

This chapter explains the basic concepts of integrity constraints.

Advantages of Integrity Constraints

An integrity constraint is a schema object that is created and dropped using SQL statements. The best practice is to enforce data integrity by means other than integrity constraints only when an integrity constraint cannot be used. Advantages of integrity constraints over alternatives for enforcing data integrity include:

• Declarative ease

  Because you define integrity constraints using SQL statements, no additional programming is required when you define or alter a table. The SQL statements are easy to write and eliminate programming errors.

• Centralized rules

  Integrity constraints are defined for tables and are stored in the data dictionary (see "Overview of the Data Dictionary"). Thus, data entered by all applications must adhere to the same integrity constraints. If the rules change at the table level, then applications need not change. Also, applications can use metadata in the data dictionary to immediately inform users of violations, even before the database checks the SQL statement.

• Flexibility when loading data

  You can disable integrity constraints temporarily to avoid performance overhead when loading large amounts of data. When the data load is complete, you can re-enable the integrity constraints.

NOT NULL Integrity Constraints

A NOT NULL constraint requires that a column of a table contain no null values. A null is the absence of a value. By default, all columns in a table allow nulls.

NOT NULL constraints are intended for columns that must not lack values. For example, the hr.employees table requires a value in the last_name column. An attempt to insert an employee row without a last name generates an error:

SQL> INSERT INTO hr.employees (employee_id, last_name) values (999, 'Smith');
.
.
.
ERROR at line 1:
ORA-01400: cannot insert NULL into ("HR"."EMPLOYEES"."LAST_NAME")

You can only add a column with a NOT NULL constraint if the table does not contain any rows or if you specify a default value.

Unique Constraints

A unique key constraint requires that every value in a column or set of columns be unique. No rows of a table may have duplicate values in a column (the unique key) or set of columns (the composite unique key) with a unique key constraint.

Unique key constraints are appropriate for any column where duplicate values are not allowed. Unique constraints differ from primary key constraints, whose purpose is to identify each table row uniquely, and typically contain values that have no significance other than being unique. Examples of unique keys include:

• A customer phone number, where the primary key is the customer number

• A department name, where the primary key is the department number

As shown in Example 2-1, a unique key constraint exists on the email column of the hr.employees table. The relevant part of the statement is as follows:

CREATE TABLE employees    ( ...
    , email          VARCHAR2(25)
        CONSTRAINT   emp_email_nn  NOT NULL ...
    , CONSTRAINT     emp_email_uk  UNIQUE (email) ... );

The emp_email_uk constraint ensures that no two employees have the same email address, as shown in Example 5-1.

SQL> SELECT employee_id, last_name, email FROM employees WHERE email = 'PFAY';
 
EMPLOYEE_ID LAST_NAME                 EMAIL
----------- ------------------------- -------------------------
        202 Fay                       PFAY

SQL> INSERT INTO employees (employee_id, last_name, email, hire_date, job_id)    
  1  VALUES (999,'Fay','PFAY',SYSDATE,'ST_CLERK');
.
.
.
ERROR at line 1:
ORA-00001: unique constraint (HR.EMP_EMAIL_UK) violated

Unless a NOT NULL constraint is also defined, a null always satisfies a unique key constraint. Thus, columns with both unique key constraints and NOT NULL constraints are typical. This combination forces the user to enter values in the unique key and eliminates the possibility that new row data conflicts with existing row data.

Primary Key Constraints

In a primary key constraint, the values in the group of one or more columns subject to the constraint uniquely identify the row. Each table can have one primary key, which in effect names the row and ensures that no duplicate rows exist.

A primary key can be natural or a surrogate. A natural key is a meaningful identifier made of existing attributes in a table. For example, a natural key could be a postal code in a lookup table. In contrast, a surrogate key key is a system-generated incrementing identifier that ensures uniqueness within a table. Typically, surrogate keys are generated by a sequence.

The Oracle Database implementation of the primary key constraint guarantees that the following statements are true:

• No two rows have duplicate values in the specified column or set of columns.

• The primary key columns do not allow nulls.

A typical situation calling for a primary key is the numeric identifier for an employee. Each employee must have a unique ID. A employee must be described by one and only one row in the employees table.

Example 5-1 indicates that an existing employee has the employee ID of 202, where the employee ID is the primary key. The following example shows an attempt to add an employee with the same employee ID and an employee with no ID:

SQL> INSERT INTO employees (employee_id, last_name, email, hire_date, job_id)    
  1  VALUES (202,'Chan','ICHAN',SYSDATE,'ST_CLERK');
.
.
.
ERROR at line 1:
ORA-00001: unique constraint (HR.EMP_EMP_ID_PK) violated

SQL> INSERT INTO employees (last_name) VALUES ('Chan');
.
.
.
ERROR at line 1:
ORA-01400: cannot insert NULL into ("HR"."EMPLOYEES"."EMPLOYEE_ID")

The database enforces primary key constraints with an index. Usually, a primary key constraint created for a column implicitly creates a unique index and a NOT NULL constraint. Note the following exceptions to this rule:

• In some cases, as when you create a primary key with a deferrable constraint, the generated index is not unique.

  Note:

  You can explicitly create a unique index with the CREATE UNIQUE INDEX statement.

• If a usable index exists when a primary key constraint is created, then the constraint reuses this index and does not implicitly create a new one.

By default the name of the implicitly created index is the name of the primary key constraint. You can also specify a user-defined name for an index. You can specify storage options for the index by including the ENABLE clause in the CREATE TABLE or ALTER TABLE statement used to create the constraint.

Foreign Key Constraints

Whenever two tables contain one or more common columns, Oracle Database can enforce the relationship between the two tables through a foreign key constraint, also called a referential integrity constraint. The constraint requires that for each value in the column on which the constraint is defined, the value in the other specified other table and column must match. An example of a referential integrity rule is an employee can work for only an existing department.

Table 5-2 lists terms associated with referential integrity constraints.

Figure 5-1 shows a foreign key on the employees.department_id column. It guarantees that every value in this column must match a value in the departments.department_id column. Thus, no erroneous department numbers can exist in the employees.department_id column.

Figure 5-1 Referential Integrity Constraints

Description of "Figure 5-1 Referential Integrity Constraints"

Self-Referential Integrity Constraints

Figure 5-2 shows a self-referential integrity constraint. In this case, a foreign key references a parent key in the same table.

In Figure 5-2, the referential integrity constraint ensures that every value in the employees.manager_id column corresponds to an existing value in the employees.employee_id column. For example, the manager for employee 102 must exist in the employees table. This constraint eliminates the possibility of erroneous employee numbers in the manager_id column.

Figure 5-2 Single Table Referential Constraints

Description of "Figure 5-2 Single Table Referential Constraints"

Check Constraints

A check constraint on a column or set of columns requires that a specified condition be true or unknown for every row. If DML results in the condition of the constraint evaluating to false, then the SQL statement is rolled back.

The chief benefit of check constraints is the ability to enforce very specific integrity rules. For example, you could use check constraints to enforce the following rules in the hr.employees table:

• The salary column must not have a value greater than 10000.

• The commission column must have a value that is not greater than the salary.

The following example creates a maximum salary constraint on employees and demonstrates what happens when a statement attempts to insert a row containing a salary that exceeds the maximum:

SQL> ALTER TABLE employees ADD CONSTRAINT max_emp_sal CHECK (salary < 10001);
SQL> INSERT INTO employees (employee_id,last_name,email,hire_date,job_id,salary)
  1  VALUES (999,'Green','BGREEN',SYSDATE,'ST_CLERK',20000);
.
.
.
ERROR at line 1:
ORA-02290: check constraint (HR.MAX_EMP_SAL) violated

A single column can have multiple check constraints that reference the column in its definition. For example, the salary column could have one constraint that prevents values over 10000 and a separate constraint that prevents values less than 500.

If multiple check constraints exist for a column, then they must be designed so their purposes do not conflict. No order of evaluation of the conditions can be assumed. The database does not verify that check conditions are not mutually exclusive.

Checks for Modified and Existing Data

The database enables you to specify whether a constraint applies to existing data or future data. If a constraint is enabled, then the database checks new data as it is entered or updated. Data that does not conform to the constraint cannot enter the database. For example, enabling a NOT NULL constraint on employees.department_id guarantees that every future row has a department ID. If a constraint is disabled, then the table can contain rows that violate the constraint.

You can set constraints to validate (VALIDATE) or not validate (NOVALIDATE) existing data. If VALIDATE is specified, then existing data must conform to the constraint. For example, enabling a NOT NULL constraint on employees.department_id and setting it to VALIDATE checks that every existing row has a department ID. If NOVALIDATE is specified, then existing data need not conform to the constraint.

The behavior of VALIDATE and NOVALIDATE always depends on whether the constraint is enabled or disabled. Table 5-4 summarizes the relationships.

Deferrable Constraints

Every constraint is either in a not deferrable (default) or deferrable state. This state determines when Oracle Database checks the constraint for validity.

Examples of Constraint Checking

Some examples may help illustrate when Oracle Database performs the checking of constraints. Assume the following:

• The employees table has the structure shown in Figure 5-2.

• The self-referential constraint makes entries in the manager_id column dependent on the values of the employee_id column.

An Update of All Foreign Key and Parent Key Values

Consider the same self-referential integrity constraint in a different scenario. The company has been sold. Because of this sale, all employee numbers must be updated to be the current value plus 5000 to coordinate with the employee numbers of the new company. Because manager numbers are really employee numbers (see Figure 5-3), the manager numbers must also increase by 5000.

Figure 5-3 The employees Table Before Updates

Description of "Figure 5-3 The employees Table Before Updates"

You could execute the following SQL statement to update the values:

UPDATE employees SET employee_id = employee_id + 5000,
  manager_id = manager_id + 5000;

Although a constraint is defined to verify that each manager_id value matches an employee_id value, the preceding statement is legal because the database effectively checks constraints after the statement completes. Figure 5-4 shows that the database performs the actions of the entire SQL statement before checking constraints.

Figure 5-4 Constraint Checking

Description of "Figure 5-4 Constraint Checking"

The examples in this section illustrate the constraint checking mechanism during INSERT and UPDATE statements, but the database uses same mechanism for all types of DML statements. The same mechanism is used for all types of constraints, not just self-referential constraints.

Note:

Operations on a view or synonym are subject to the integrity constraints defined on the underlying base tables.