Skip to content

SQL duplicate prevention pattern

The SQL duplicate prevention pattern is a database operation strategy, specifically demonstrated within Oracle environments using Database Links. It ensures data integrity during data migration or synchronization by preventing the insertion of records that already exist in the target table.^[600-developer__database__oracle__oracle-db-link.md]

Implementation Logic

The core mechanism involves constructing a SELECT statement that retrieves data from the source (remote) database while explicitly excluding rows that are present in the destination (local) database.^[600-developer__database__oracle__oracle-db-link.md]

This is achieved by using a NOT IN or NOT EXISTS clause (often implemented via WHERE ... NOT IN subqueries) to filter the result set before insertion.^[600-developer__database__oracle__oracle-db-link.md] The filtered data is then inserted into the local table using an INSERT INTO ... SELECT ... statement.^[600-developer__database__oracle__oracle-db-link.md]

Usage Example

In a typical data recovery or synchronization scenario involving an Oracle Database Link, the pattern is applied as follows:

  1. Identify the Source: Select columns from the remote table via the database link (e.g., SELECT c.* FROM py_order@mypaycenter c).^[600-developer__database__oracle__oracle-db-link.md]
  2. Define Scope: Filter the remote data by specific criteria, such as a Customer ID (CUST_ID) and a range of Order Numbers (order_no).^[600-developer__database__oracle__oracle-db-link.md]
  3. Exclude Existing Data: Apply a condition to ignore rows where the primary key (e.g., order_no) already matches an entry in the local table (e.g., and c.order_no not in (SELECT p.order_no FROM py_order p...)).^[600-developer__database__oracle__oracle-db-link.md]
  4. Execute Insert: Insert the final deduplicated set into the local table.^[600-developer__database__oracle__oracle-db-link.md]

This approach allows for safe, idempotent execution of data transfer scripts, ensuring that re-running the operation does not cause primary key violations or create duplicate entries.^[600-developer__database__oracle__oracle-db-link.md]

Sources

^[600-developer__database__oracle__oracle-db-link.md]