The deferrable constraint was introduces for a process that makes a bunch of updates to a lot of tables that temporarily makes the tables "inconsistent" but by the time its done -- every thing will be OK.

Lets say you were writing a batch process. It would process input files that told it to INSERT/UPDATE/DELETE employee and dept records. It is going to do this work in some order (maybe process ALL emp changes and then ALL dept changes for example). During the processing it is told to:

DELETE employee 1 (who is the manager of employee 2)
UPDATE employee 2 setting the manager to employee 3, department to 50
INSERT employee 3 into deptartment 50

INSERT department 50
DELETE department 10 (employee 1 and 2 were the only ones in dept 10)

At the END of the process -- all of the tables are OK. Individually however -- each statement would leave the database in an inconsistent state. If you delete employee 1 -- you have an orphaned foreign key in the employee table UNTIL you create employee 3 but employee 3 is in dept 50 which doesn't exist until you create it.

So, during this process the database is inconsistent -- at the end it is OK. A common use of this might be to do an update cascade. Say you wanted to change dept = 10 to dept = 11. If you update the DEPT table first -- it would normally fail since the emp table has employees in dept 10. If you tried the emp table first, it would fail since dept = 11 doesn't exist. deferrable constraints permit this to take place naturally. the constraints are validates upon COMMIT.



the main difference between an immediate and deferred constraint is quite simple: an immediate constraint is validated after each statement, a deferred constraint is validated upon commit (or upon setting all constraints to immediate).


Search for deferrable on my site to see examples. immediate is the default, you have to define constraints as deferrable in the CREATE or ALTER statement.