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:
- The parent table of the foreign key constraint is a snapshot.
- 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.
- 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.