Oracle7 Server Concepts

The Mechanisms of Constraint Checking

To know what types of actions are permitted when constraints are present, it is useful to understand when Oracle actually performs the checking of constraints. To illustrate this, an example or two is helpful. Assume the following:

The EMP table has been defined as illustrated in a previous example (see Figure 7 - 7).

The self-referential constraint makes the entries in the MGR column dependent on the values of the EMPNO column. For simplicity, the rest of this discussion only addresses the EMPNO and MGR columns of the EMP table.

Consider the insertion of the first row into the EMP table. No rows currently exist, so how can a row be entered if the value in the MGR column cannot reference any existing value in the EMPNO column? The three possibilities include the following:

A null can be entered for the MGR column of the first row, assuming that the MGR column does not have a NOT NULL constraint defined on it.

The same value can be entered in both the EMPNO and MGR columns.

A multiple row INSERT statement, such as an INSERT statement with nested SELECT statement, can insert rows that reference one another. For example, the first row might have EMPNO as 200 and MGR as 300, while the second row might have EMPNO as 300 and MGR as 200.

Each case reveals something about how and when Oracle performs constraint checking.

The first case is easy to understand; a null is given for the foreign key value. Because nulls are allowed in foreign keys, this row is inserted successfully into the table.

The second case is more interesting. This case reveals when Oracle effectively performs its constraint checking: after the statement has been completely executed. To allow a row to be entered with the same values in the parent key and the foreign key, Oracle must first execute the statement (that is, insert the new row) and then check to see if any row in the table has an EMPNO that corresponds to the new row's MGR.

The third case reveals even more about the constraint checking mechanism. This scenario shows that constraint checking is effectively deferred until the complete execution of the statement; all rows are inserted first, then all rows are checked for constraint violations.

As another example of this third case, consider the same self-referential integrity constraint and the following 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 new company's employee numbers. Because manager numbers are really employee numbers, these values must also increase by 5000.

The table currently exists as illustrated in Figure 7 - 8.

Figure 7 - 8. The EMP Table Before Updates

UPDATE emp

	SET empno = empno + 5000,

	    mgr = mgr + 5000;

Even though a constraint is defined to verify that each MGR value matches an EMPNO value, this statement is legal because Oracle effectively performs its constraint checking after the statement completes. Figure 7 - 9 shows that Oracle performs the actions of the entire SQL statement before any constraints are checked.

Figure 7 - 9. Constraint Checking

The examples in this section illustrated the constraint checking mechanism during INSERT and UPDATE statements. The same mechanism is used for all types of DML statements, including UPDATE, INSERT, and DELETE statements.

The examples also used self-referential integrity constraints to illustrate the checking mechanism. However, the same mechanism is used for all types of constraints, including NOT NULL, UNIQUE key, PRIMARY KEY, all types of FOREIGN KEY, and CHECK constraints.

Default Column Values and Integrity Constraint Checking

Default values are included as part of an INSERT statement before the statement is parsed. Therefore, default column values are subject to all integrity constraint checking.