Deferred constraint checking: Whenever there is a change in the database, constraints are checked. In the process of deferred constraint checking, constraints checking is deferred or postponed until the end of a SQL transaction. SQL COMMIT statement signals that the transaction is completed and after that constraints are checked. If constraints are satisfied then COMMIT statement is executed, otherwise the transaction is rolled back. Deferred constraint checking is helpful for situations where two or more statements have to be executed in order to make a particular change in the database. A constraint can be of two types: DEFERRABLE constraint and NOT DEFERRABLE constraint. By default a constraint is NOT DEFERRABLE.
DEFERRABLE constraint: For a DEFERRABLE constraint, checking can be postponed until the end of the transaction. Assertion is a type of DEFERRABLE constraint.
NOT DEFERRABLE constraint: For a NOT DEFERRABLE constraint, checking cannot be postponed until the end of the transaction. Uniqueness constraint and primary key constraint are types of NOT DEFERRABLE constraint.
Types of constraints based on initial state: INITIALLY IMMEDIATE constraint and INITIALLY DEFERRED constraint. By default a constraint is INITIALLY IMMEDIATE constraint.
INITIALLY IMMEDIATE constraint: INITIALLY IMMEDIATE constraint is checked immediately for every statement.
INITIALLY DEFERRED constraint: For an INITIALLY DEFERRED constraint, checking is postponed until the end of a transaction.