Update PK - Deferrable constraints

http://www.idevelopment.info/data/Oracle/DBA_tips/Database_Administration/DBA_12.shtml

Oracle Constraints - All in one page details
http://psoug.org/reference/constraints.html

Deferred Integrity Constraints in Oracle8

by Jeff Hunter, Sr. Database Administrator

Introduction

Up until the release of Oracle8, all integrity constraints in the database where considered immediate constraints. With an immediate constraint, if a user enters data that violates a constraint, the statement is rolled back and Oracle signals an error. With the introduction of Oracle8, users now have the option of selecting between immediate and a new option called deferred itegrity constraints.
Deferred Integrity Constraints
Deferred integrity constraints are those that are not enforced until the user attempts to commit the transaction. At the time of the commit, if any data entered during the transaction violates an integrity constraint, Oracle will then rollback the entire transaction and signal an error to the user.
Why use deferrable integrity constraints?
One use is pure laziness. In Oracle7, users had no way of entering data into a child table before entering the matching data into the parent table. With Oracle8, users now have the option of inserting data into a child table before insert its matching data into the parent table by simply deferring the foreign-key constraint.

Another feature is speed. Anyone that has ever dealt with inserting large amounts of data into a table, like found in a data warehousing environment, know of the cost loading (inserting) data into tables. Deferring the integrity checking until after the data has loaded can save considerable time when dealing with large volumes of data being inserted into tables.

Solving design issues. Take for example the two tables ORDER and ORDER LINE. In this model, we cannot have an ORDER LINE unless we have an ORDER, but we also assert that all ORDERS must have ORDER LINES. This catch-22 issue can easily be resolved by deferring all integrity constraints until the time of the commit.
Warnings
Deferred integrity constraints should only be used when absolutely necessary. If at all possible, avoid hard-coding constraint names with your application code. Deferring integrity constraints should only be used when solving a design issue. Be aware that very few applications support commit time errors.
Statements
To defer ALL constraints that are deferrable during the entire session, you can use the alter session set constraints=deferred statement.

A user can defer a named or all constraints that are deferrable for a specific transaction using the set constraint name deferred or set constraint all deferred.
The user process may also set constraints for immediate enforcement for a specific transaction using the set constraint name immediate or set constraint all immediate.
Are all constraints deferrable?
Not all constraints in the database can be set to deferrable. Only when the constraint is created with the deferrable option can user processes set the constraint as deferrable. If the constraint is created with the not deferrable option (the default), then user processes will always be bound by immediate integrity constraint.
Default behavior of constraints
Along with defining whether a constraint is deferrable or not, you can also set its default behavior with the initially deferred and initially immediate statement. The initially deferred option will enable the constraint as deferrable by default whereas the initially immediate option requires the user process to explicitly set the transaction or session to deferrable.
NOTE: The initially deferred and not deferrable keyword are mutually exclusive.


New columns in the CONSTRAINTS view
There are new columns in the "USER_CONSTRAINTS/DBA_CONSTRAINTS/ALL_CONSTRAINTS" views that will give you information on whether the constraint is deferrable. For example:

SELECT
constraint_name
, deferrable
, deferred
FROM user_constraints
WHERE constraint_name like 'EMP%';

CONSTRAINT_NAME DEFERRABLE DEFERRED
------------------------------ -------------- ---------
EMP_PRIMARY_KEY NOT DEFERRABLE IMMEDIATE
EMP_SELF_KEY NOT DEFERRABLE IMMEDIATE
EMP_FOREIGN_KEY DEFERRABLE DEFERRED


Example

REM -- ---------------------
REM -- Create example tables
REM -- ---------------------

DROP TABLE emp;
DROP TABLE dept;

CREATE TABLE dept (
deptno NUMBER(2) NOT NULL
, dname CHAR(14)
, loc CHAR(13)
, CONSTRAINT dept_pk PRIMARY KEY (deptno)
);

INSERT INTO dept VALUES (10,'FINANCE','PITTSBURGH');
INSERT INTO dept VALUES (20,'SALES','NEW YORK');
INSERT INTO dept VALUES (30,'OPERATIONS','BOSTON');

COMMIT;

CREATE TABLE emp (
empno NUMBER(4) NOT NULL
, ename CHAR(10)
, job CHAR(9)
, deptno NUMBER(2) NOT NULL
, CONSTRAINT emp_fk1 FOREIGN KEY (deptno)
REFERENCES dept (deptno)
DEFERRABLE
INITIALLY IMMEDIATE
, CONSTRAINT emp_pk PRIMARY KEY (empno)
);

INSERT INTO emp VALUES (1001, 'JEFF', 'PRESIDENT', 10);
INSERT INTO emp VALUES (1002, 'MELODY', 'MANAGER', 30);
INSERT INTO emp VALUES (1003, 'MARK', 'MANAGER', 10);
INSERT INTO emp VALUES (1004, 'MARTIN', 'MANAGER', 20);

COMMIT;

REM -- -------------------------------
REM -- Try to delete from parent table
REM -- -------------------------------

DELETE FROM dept WHERE deptno = 10;

ERROR at line 1:
ORA-02292: integrity constraint (OPS$ORACLE.EMP_FK1) violated - child record found

REM -- --------------------------------------------------------
REM -- Set this transaction to defer all deferrable constraints
REM -- --------------------------------------------------------

SET CONSTRAINTS ALL DEFERRED;

DELETE FROM dept WHERE deptno = 10;

1 row deleted.

COMMIT;

ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02292: integrity constraint (OPS$ORACLE.EMP_FK1) violated - child record found

Toms Discussion,
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:914629004506