Using Deferred Constraints with Oracle GoldenGate Replication in Oracle 23c

Oracle constraints enforce rules for data integrity, such as uniqueness and referential relationships between tables. In a traditional snapshot-based replication setup, constraints could interfere with data refresh operations because dependent data might not be fully inserted until the final `COMMIT`. This led to the need for deferring constraints during updateable snapshot refreshes.
With Oracle GoldenGate, which is optimized for Oracle Database 23c, constraint enforcement is handled more efficiently. Since GoldenGate replicates DML operations (INSERT, UPDATE, DELETE) in real-time or near-real-time, and preserves transactional consistency, constraint violations can still occur, especially in multi-master (active-active) replication scenarios or when foreign key dependencies are present.
To mitigate this, you can still defer constraint checking until the end of the transaction using :deferrable constraints:, just as you would in a transactional application. This is particularly useful in the following situations:

• The parent table of a foreign key is also being replicated asynchronously.
• Large-scale data operations might temporarily violate constraints until the full transaction is complete.
• Cascading updates (e.g., via ON UPDATE CASCADE) are triggered during replication events.

Setting Up a Deferrable Constraint
Unlike legacy snapshots, GoldenGate does not require deferred constraint logic to be tied to snapshot refreshes. Instead, constraints are handled natively at the database level, and you can configure them as *deferrable* to work smoothly during replicated transactions.
To define a deferrable primary key constraint on `CUSTOMER_OBJ_TABLE`:

-- Drop existing constraint if needed
ALTER TABLE CUSTOMER_OBJ_TABLE DROP CONSTRAINT cust_pk;

-- Recreate the constraint as deferrable
ALTER TABLE CUSTOMER_OBJ_TABLE
ADD CONSTRAINT cust_pk PRIMARY KEY (customer_id)
DEFERRABLE INITIALLY DEFERRED;

During replication or application transactions, Oracle will now :enforce the constraint only at COMMIT time:, allowing GoldenGate or application logic to complete the full data operation first.
🔧 Note: You must define constraints as deferrable at creation time. The `ALTER TABLE ... MODIFY CONSTRAINT` syntax does :not: allow modifying an existing constraint to be deferrable. The constraint must be dropped and recreated.

Constraint violations can occur in GoldenGate replication, particularly in multi-master (active-active) setups or with foreign key dependencies. This happens because:

• Multi-Master Conflicts: In active-active replication, simultaneous DML operations on the same data at different sites can lead to conflicts (e.g., duplicate primary keys or updates to the same row). GoldenGate doesn't inherently resolve these conflicts unless configured with conflict detection and resolution (CDR) logic.
• Foreign Key Violations: If data is replicated out of order or if parent-child relationships aren't synchronized properly, foreign key constraints can be violated. For example, an INSERT into a child table may arrive before the corresponding parent table record.
• Timing and Latency: Even in near-real-time replication, slight delays can cause temporary inconsistencies, especially if transactions are applied out of sequence or if there’s network latency.

Mitigation Strategies

• Conflict Detection and Resolution (CDR): Configure GoldenGate to detect conflicts (e.g., using COMPARECOLS or RESOLVECONFLICT) and define resolution rules (e.g., timestamp-based or site-priority-based).
• Proper Transaction Ordering: Ensure GoldenGate maintains transactional consistency using features like PASSTHRU or coordinated apply to preserve the order of operations.
• Deferred Constraints: Set foreign key constraints to DEFERRABLE on the target database to allow temporary inconsistencies during replication, resolving them at transaction commit.
• Data Filtering: Use GoldenGate filters to exclude or transform problematic DML operations that could violate constraints.
• Error Handling: Implement exception handling in GoldenGate (e.g., HANDLECOLLISIONS or REPORTERROR) to log and manage constraint violations for manual resolution.
• Schema Design: Minimize constraints in active-active setups or design schemas to avoid dependencies that could lead to violations.

1. Drop Non-Replicable Constraints if Necessary
   • If constraints were created without consideration for replication (e.g., complex or deferrable constraints that interfere with GoldenGate Apply), consider dropping them or modifying them. For example:

               ALTER TABLE CUSTOMER_OBJ_TABLE DROP CONSTRAINT CUSTOMER_OBJ_PK;
             

2. Recreate Constraints with Replication in Mind
   • Recreate the constraint in a way that is compatible with GoldenGate replication. In many use cases, constraints are kept disabled at the target to allow for deferred validation or re-validation post-apply:

               ALTER TABLE CUSTOMER_OBJ_TABLE ADD CONSTRAINT CUSTOMER_OBJ_PK
               PRIMARY KEY (CUST_ID) DISABLE NOVALIDATE;
             

3. Mark Constraint for Deferred Validation Post Replication
   • Use DISABLE NOVALIDATE so that GoldenGate can insert/update rows without firing the constraint, yet the definition remains in metadata:

               -- Constraint is defined, but not enforced during replication
             

4. Apply the Constraint Definition in Target System Metadata
   • Execute the constraint definition on the target database where GoldenGate replicates the data. This helps retain schema parity without enforcing immediate validation.
5. Control Constraint Checks with GoldenGate Parameters
   • In your GoldenGate Replicat parameter file, use:

               ASSUMETARGETDEFS
               DISABLEINTEGERS
             

     Or selectively disable integrity checks using:

               MAP ..., TARGET ..., INSERTALLRECORDS;
             

6. Use HANDLECOLLISIONS or DDL INCLUDE for Initial Loads
   • During initial loads or one-time syncs, avoid constraint errors by including:

               HANDLECOLLISIONS
               DDL INCLUDE ALL
             

7. Re-enable and Validate Constraints After Apply Completes
   • After replication is complete, optionally validate the constraint manually:

               ALTER TABLE CUSTOMER_OBJ_TABLE ENABLE VALIDATE CONSTRAINT CUSTOMER_OBJ_PK;
             

8. Confirm Constraint Validity
   • You have now:
     • Dropped incompatible constraints.
     • Recreated them for replication awareness.
     • Disabled enforcement during replication.
     • Re-enabled and validated them after replication.

⚠️ Notes

• GoldenGate prefers constraints to be disabled during replication to avoid replication errors due to out-of-order transactions.
• If source and target tables are identically structured, constraint enforcement should be handled post-synchronization.
• If using Oracle GoldenGate for Big Data, constraint handling differs since target systems (Kafka, Hadoop) do not enforce relational constraints.

The following section discusses working with deferred constraints.

The code you should have created is listed below.
You created a deferrable foreign key constraint:

ALTER TABLE CUSTOMER_PROFILE 
ADD CONSTRAINT CUST_PROFILE_PROD_FK 
FOREIGN KEY(LAST_PRODUCT_PURCHASED) 
REFERENCES PRODUCT_INVENTORY 
DEFERRABLE INITIALLY IMMEDIATE 
/ 
-- You set the constraint to its deferred state: 
SET CONSTRAINT CUST_PROFILE_PROD_FK DEFERRED 
/ 

SET CONSTRAINTS

DESCRIPTION: