Distributed Databases   «Prev  Next»
Lesson 6 Using deferred constraints with updateable snapshots
Objective Use deferred constraints on updateable snapshots.

Using Deferred Constraints with Snapshots

Oracle constraints enforce data values and data relationships. However, these constraints can become a problem with replication because all of the constraint values may not be entered into the tables until the final COMMIT operation.
Because of this problem, Oracle now provides the ability to defer constraints with updateable snapshots, so that the constraints are only re-enabled after the entire snapshot refresh has been committed.
You may wish to defer constraint checks on unique and foreign keys if the data you are working with has any of the following characteristics:
  1. The parent table of the foreign key constraint is a snapshot.
  2. Your tables contain a large amount of data that is being manipulated by another application, which may or may not return the data in the same order.
  3. You have implemented UPDATE CASCADE operations, based on foreign keys.

Follow along in this simulation to learn how to set up and use deferred constraints. The simulation uses a table in the Course Project, the CUSTOMER_OBJ_TABLE, and sets its primary key as a deferrable constraint. The simulation then shows you how to defer the constraint until the end of a transaction.

Using Deferred Constraints with Updateable Snapshots
You cannot use the
ALTER TABLE … MODIFY CONSTRAINT
syntax to add the DEFERRABLE clause to an existing constraint.
You must drop the constraint and recreate it.
When applied to a snapshot, the DEFER option allows the entire refresh to complete before the transaction is COMMITTED.
Next, let us take a look at LOB support with replication.

SetUp Deferred Constraint On Table

Click the link below to practice working with deferred constraints.
SetUp Deferred Constraint On Table