---
# === IDENTITY ===
id: business/erp-integration/duplicate-detection-natural-keys/2026
canonical_question: "How do duplicate detection and natural key management differ across ERPs?"
aliases:
  - "Cross-ERP duplicate prevention strategies and ExternalId patterns"
  - "Natural key vs surrogate key for ERP integration deduplication"
  - "How to prevent duplicate records when syncing between ERPs"
  - "ID mapping table design for multi-system ERP integration"
entity_type: erp_integration
domain: business > erp-integration > duplicate-detection-natural-keys
region: global
jurisdiction: global
temporal_scope: 2024-2026

# === SYSTEM PROFILE ===
# Cross-system comparison card — duplicate detection and natural key management across 5 major ERPs.
systems:
  - name: "Salesforce"
    vendor: "Salesforce"
    version: "API v62.0"
    edition: "Enterprise, Unlimited"
    deployment: "cloud"
    api_surface: "REST, SOAP, Bulk API 2.0"
  - name: "Oracle NetSuite"
    vendor: "Oracle"
    version: "2024.2"
    edition: "SuiteCloud"
    deployment: "cloud"
    api_surface: "SuiteTalk REST, SuiteTalk SOAP, RESTlet"
  - name: "SAP S/4HANA"
    vendor: "SAP"
    version: "2408"
    edition: "Cloud, On-Premise"
    deployment: "hybrid"
    api_surface: "OData v4, SOAP, RFC/BAPI"
  - name: "Microsoft Dynamics 365"
    vendor: "Microsoft"
    version: "Dataverse API v9.2"
    edition: "Enterprise, Business Central"
    deployment: "cloud"
    api_surface: "OData v4, Web API"
  - name: "Oracle ERP Cloud"
    vendor: "Oracle"
    version: "24B"
    edition: "Cloud"
    deployment: "cloud"
    api_surface: "REST, SOAP, FBDI"

# === VERIFICATION ===
last_verified: 2026-03-07
confidence: 0.87
version: 1.0
first_published: 2026-03-07

# === TEMPORAL VALIDITY ===
temporal_validity:
  status: evolving
  last_breaking_change: "2025-06 — NetSuite 2024.2 tightened external ID uniqueness enforcement across shared record types"
  next_review: 2026-09-03
  change_sensitivity: medium

# === CONSTRAINTS ===
constraints:
  - "Salesforce allows max 7 External ID fields per object — plan field allocation carefully for multi-system integrations"
  - "NetSuite external IDs are case-insensitive — 'ORD-001' and 'ord-001' collide and return 400 Bad Request"
  - "NetSuite external ID is single-valued per record — only one integrated application can own external IDs per record type"
  - "SAP S/4HANA has no native external ID field — duplicate detection relies on fuzzy matching (ICM) or custom Z-fields"
  - "Dynamics 365 alternate keys support max 5 fields per key and max 10 keys per entity — field types restricted to text, number, datetime, lookup, option set"
  - "Oracle ERP Cloud OrigSystemReference allows multiple source system references per record but requires explicit source system registration"
  - "Alternate key special characters in D365 (/,<,>,*,%,&,:,\\,?,+) break retrieve/update/upsert operations"

# === SKIP CONDITIONS ===
skip_this_unit_if:
  - condition: "User needs idempotency patterns (retry safety, at-least-once processing)"
    use_instead: "business/erp-integration/idempotency-erp-integrations/2026"
  - condition: "User needs single-system API reference (endpoints, rate limits, auth)"
    use_instead: "business/erp-integration/{system}-rest-api-capabilities/2026"
  - condition: "User needs master data management strategy (golden record, data stewardship)"
    use_instead: "business/erp-integration/master-data-management-erp/2026"

# === AGENT HINTS ===
inputs_needed:
  - key: erp_systems
    question: "Which ERP systems are you integrating?"
    type: choice
    options:
      - "Salesforce"
      - "SAP S/4HANA"
      - "Oracle NetSuite"
      - "Microsoft Dynamics 365"
      - "Oracle ERP Cloud"
      - "Multiple / cross-system"
  - key: record_types
    question: "What record types need deduplication?"
    type: choice
    options:
      - "Customer/Account records"
      - "Product/Item records"
      - "Order/Transaction records"
      - "Invoice/Financial records"
      - "Mixed — multiple object types"
  - key: id_strategy
    question: "Do you already have a cross-system ID strategy?"
    type: choice
    options:
      - "No — need to design one"
      - "Yes — using native ExternalId/alternate key"
      - "Yes — using a custom cross-reference mapping table"
      - "Yes — using natural business keys (PO number, SKU, etc.)"

# === DISTRIBUTION ===
canonical_source: "https://knowledgelib.io/business/erp-integration/duplicate-detection-natural-keys/2026"
suggested_citation: "Source: knowledgelib.io — AI Knowledge Library (verified 2026-03-07)"

# === RELATED UNITS ===
related_kos:
  depends_on:
    - id: "business/erp-integration/idempotency-erp-integrations/2026"
      label: "Idempotency patterns — complementary to duplicate detection (retry safety vs record uniqueness)"
  related_to:
    - id: "business/erp-integration/master-data-management-erp/2026"
      label: "Master Data Management — golden record strategy builds on duplicate detection"
    - id: "business/erp-integration/field-mapping-data-type-comparison/2026"
      label: "Field mapping — natural key fields must survive data type transformations"
  solves:
    - id: "business/erp-integration/salesforce-netsuite-integration/2026"
      label: "Salesforce-NetSuite sync requires cross-system ID mapping for dedup"
    - id: "business/erp-integration/salesforce-sap-integration/2026"
      label: "Salesforce-SAP sync requires external ID strategy for bidirectional dedup"
  alternative_to: []
  often_confused_with:
    - id: "business/erp-integration/idempotency-erp-integrations/2026"
      label: "Idempotency is about safe retries; duplicate detection is about record uniqueness — related but different problems"

# === SOURCES ===
sources:
  - id: src1
    title: "Insert or Update (Upsert) a Record Using an External ID"
    author: Salesforce
    url: https://developer.salesforce.com/docs/atlas.en-us.api_rest.meta/api_rest/dome_upsert.htm
    type: official_docs
    published: 2025-12-01
    reliability: authoritative
  - id: src2
    title: "External IDs Overview — NetSuite Applications Suite"
    author: Oracle
    url: https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/section_N3433806.html
    type: official_docs
    published: 2025-06-01
    reliability: authoritative
  - id: src3
    title: "SAP S/4HANA Business Partner Duplicate Check"
    author: SAP
    url: https://community.sap.com/t5/enterprise-resource-planning-blog-posts-by-sap/sap-s-4hana-business-partner-duplicate-check/ba-p/13494543
    type: technical_blog
    published: 2024-08-15
    reliability: high
  - id: src4
    title: "Dataverse: Duplicate Detection Rules vs Alternate Keys"
    author: Dynamics Chronicles
    url: https://dynamics-chronicles.com/article/dataverse-duplicate-detection-rules-vs-alternate-keys
    type: community_resource
    published: 2024-11-20
    reliability: moderate_high
  - id: src5
    title: "Import Your Hub Source System Reference Data — Oracle Fusion Cloud"
    author: Oracle
    url: https://docs.oracle.com/en/cloud/saas/customer-data-management/faudm/import-your-hub-source-system-reference-data.html
    type: official_docs
    published: 2025-03-01
    reliability: authoritative
  - id: src6
    title: "Improving Dynamics 365 Data Integrations with Alternate Keys"
    author: CRM Software Blog
    url: https://www.crmsoftwareblog.com/2021/01/improving-dynamics-365-data-integrations-with-alternate-keys/
    type: technical_blog
    published: 2024-01-15
    reliability: moderate_high
  - id: src7
    title: "Introduction to Cross Reference Tables — Oracle SOA Suite"
    author: Oracle
    url: https://docs.oracle.com/en/middleware/soa-suite/soa/14.1.2/develop/introduction-cross-reference-tables.html
    type: official_docs
    published: 2024-06-01
    reliability: authoritative
  - id: src8
    title: "NetSuite: SuiteTalk REST and External IDs"
    author: Tim Dietrich
    url: https://timdietrich.me/blog/netsuite-suitetalk-rest-external-ids/
    type: technical_blog
    published: 2024-09-10
    reliability: moderate_high
---

# Duplicate Detection and Natural Key Management Across ERPs

## TL;DR

- **Bottom line**: Every ERP handles duplicate detection differently — Salesforce and NetSuite provide native ExternalId upsert, Dynamics 365 uses alternate keys at the database level, SAP relies on fuzzy matching or custom fields, and Oracle ERP Cloud uses OrigSystemReference with source system registration. Choose your ID strategy before writing integration code.
- **Key limit**: Salesforce allows max 7 External ID fields per object; NetSuite allows only 1 external ID value per record (single-owner); Dynamics 365 alternate keys support max 5 fields per key with max 10 keys per entity. [src1, src2, src4]
- **Watch out for**: NetSuite external IDs are case-insensitive — `ORD-001` and `ord-001` collide silently, causing 400 errors in production. [src2]
- **Best for**: Integration architects designing cross-system sync, middleware teams building ID mapping strategies, and developers implementing upsert logic across heterogeneous ERP landscapes.
- **Decision**: Use native ExternalId/alternate key when integrating 2 systems; build a cross-reference mapping table when integrating 3+ systems or when no native mechanism exists (SAP).

## System Profile

This card compares duplicate detection and natural key management across five major ERP systems. Each system takes a fundamentally different approach: Salesforce and NetSuite provide first-class External ID fields with native upsert support, Microsoft Dynamics 365 enforces uniqueness through database-level alternate keys, SAP S/4HANA relies on fuzzy matching (ICM) for business partners and custom Z-fields for other entities, and Oracle ERP Cloud uses a source system reference model (OrigSystemReference/HZ_ORIG_SYS_REFERENCES) that supports multiple source system registrations per record.

| System | Role | Duplicate Detection Approach | Native Upsert? |
|---|---|---|---|
| Salesforce | CRM / Platform | ExternalId fields + Duplicate Rules | Yes — PATCH upsert by ExternalId |
| Oracle NetSuite | ERP / Financials | ExternalId + internalId | Yes — upsert/upsertList operations |
| SAP S/4HANA | ERP / Manufacturing | Fuzzy matching (ICM) + custom Z-fields | No native upsert — use BAPI + custom logic |
| Microsoft Dynamics 365 | CRM / ERP | Alternate Keys (DB index) + Duplicate Detection Rules | Yes — PATCH upsert by alternate key |
| Oracle ERP Cloud | ERP / Financials | OrigSystemReference + Source System Registration | Partial — FBDI merge, REST create-or-update |

## Per-ERP Duplicate Prevention

### Salesforce: ExternalId + Duplicate Rules

Salesforce provides the most integration-friendly duplicate prevention. Custom fields can be marked as `External ID` (checkbox) and optionally `Unique` (checkbox). An External ID field that is also marked Unique creates a database-level unique index, enabling deterministic upsert. [src1]

**How it works**: The REST API upsert endpoint (`PATCH /services/data/v62.0/sobjects/{Object}/{ExtIdField}/{ExtIdValue}`) performs an atomic insert-or-update. If a record with the given External ID value exists, it updates; otherwise, it creates. This is the gold standard for integration dedup. [src1]

**Duplicate Rules** (separate feature): Configurable matching rules that fire on create/update via UI. By default, Duplicate Rules do NOT fire on API operations — you must explicitly set the `Sforce-Duplicate-Rule-Header` to `false` to enable them. Duplicate Rules are advisory (users can override) whereas External ID uniqueness is absolute. [src1]

**Limits**: Max 7 External ID fields per standard object, 25 per custom object. External ID fields count toward the custom field limit. Each External ID field adds an index, impacting DML performance on high-volume objects. [src1]

### Oracle NetSuite: ExternalId + internalId

NetSuite uses a single `externalId` attribute on most record types, set via SOAP web services (SuiteTalk). The `upsert` and `upsertList` operations match on externalId — if found, update; if not, create. [src2]

**Critical constraint**: Each record can have only ONE externalId value, and only one integrated application should own the externalId for a given record type. If multiple integration partners need to reference the same record, only one can use externalId — others must use a custom field or the internal numeric ID. [src2]

**Case sensitivity**: External IDs in NetSuite are case-insensitive. `CUST-001` and `cust-001` are treated as the same value. This causes silent collisions if your source systems use case-sensitive IDs. [src2]

**REST API**: Use `eid:` prefix in URLs to reference records by external ID — e.g., `GET /record/v1/customer/eid:CUST-001`. [src8]

**Not supported on**: Accounting Periods, Budget Categories, Custom Field types, Custom Lists, Custom Record Types, Gift Certificates, Landed Costs, State records. SuiteScript does not support external IDs — only SOAP web services. [src2]

### SAP S/4HANA: Fuzzy Matching (ICM) + Custom Z-Fields

SAP S/4HANA has NO native External ID field for arbitrary record types. Duplicate detection varies by object type: [src3]

**Business Partners (BP)**: SAP provides built-in fuzzy duplicate checking using Information Consistency Management (ICM). The system evaluates address data, name fields, and other attributes using configurable threshold values (0-100). A threshold of 90 means 90% data similarity triggers a duplicate flag. This fires on BP creation/modification in both GUI (transaction BP) and via BAPI calls. [src3]

**Other entities (Materials, Orders, etc.)**: No built-in duplicate check. Integration teams must either:
1. Create custom Z-fields (e.g., `ZEXT_SYSTEM_ID`) and implement custom ABAP duplicate checks via user exits or BAdIs
2. Implement deduplication in middleware (SAP Integration Suite Idempotent Process Call, or iPaaS-level dedup) [src3]

**BAPI considerations**: BAPIs are not inherently idempotent. Calling `BAPI_BUPA_CREATE_FROM_DATA` twice with the same data creates two business partners. For idempotent creates, you must first call a search BAPI (e.g., `BAPI_BUPA_SEARCH`) to check for existing records, then conditionally create or update. [src3]

### Microsoft Dynamics 365: Alternate Keys + Duplicate Detection Rules

Dynamics 365 (Dataverse) provides two distinct mechanisms that serve different purposes: [src4]

**Alternate Keys**: Database-level unique indexes. Once defined, they enforce absolute uniqueness — no record can violate the constraint, whether created via UI, API, or import. Alternate keys enable upsert via the Web API: `PATCH /api/data/v9.2/{entityset}({keyfield}='{value}')`. If a record matches the key, it updates; otherwise, it creates. [src4, src6]

**Duplicate Detection Rules**: Application-level matching with configurable criteria (exact match, same first N characters, date matching). By default, these do NOT apply to Web API/SDK operations. To enable them programmatically, set `MSCRM.SuppressDuplicateDetection: false` in the request header. Users can override duplicate warnings — rules are advisory, not absolute. [src4]

**Key restrictions**: Max 10 alternate keys per entity, max 5 fields per key. Only certain field types allowed: Decimal Number, Whole Number, Single Line of Text, DateTime, Lookup, Option Set. Special characters (`/,<,>,*,%,&,:,\,?,+`) in key field values break retrieve/update/upsert operations. [src4, src6]

### Oracle ERP Cloud: OrigSystemReference + Source System Registration

Oracle Fusion Cloud uses a source system reference model. Records in Oracle Fusion are tracked through `HZ_ORIG_SYS_REFERENCES`, which maps source system names and source system reference IDs to internal Oracle party/object IDs. [src5]

**Multiple source references**: Unlike Salesforce or NetSuite (which have a single External ID), Oracle allows multiple source system references per record. A customer record can simultaneously have references from SAP (`SAP:BP-1001`), Salesforce (`SF:ACC-2002`), and a legacy system (`LEGACY:C-3003`). This is designed for hub-and-spoke MDM architectures. [src5]

**Source system registration**: Each source system must be explicitly registered in Oracle Fusion before it can write source system references. Unregistered systems cannot create cross-references. [src5]

**Merge behavior**: When importing data with `Allow Multiple Source System References` enabled, Oracle can merge duplicate source records into a single Oracle record. When disabled, each source record creates a separate Oracle record — which may create duplicates. [src5]

## Natural Key Design Strategies

### GUID vs Business Key vs Surrogate Key

| Strategy | Example | Pros | Cons | Best For |
|---|---|---|---|---|
| **Natural business key** | PO-2026-00142, SKU-WIDGET-BLU | Human-readable, meaningful, stable across systems | May not be unique across systems, can change (rare) | Orders, Products, Invoices |
| **GUID/UUID** | `f47ac10b-58cc-4372-a567-0e02b2c3d479` | Globally unique, no coordination needed | Not human-readable, large (36 chars), no semantic meaning | New greenfield systems, event IDs |
| **Composite key** | `SF:ACC-1001` (system + local ID) | Unique across systems, traceable to source | Requires consistent formatting, longer values | Cross-system mapping with 2 systems |
| **Surrogate + mapping table** | Internal integer ID + xref table | Decouples systems, handles N-to-N mappings | Additional infrastructure, lookup overhead | 3+ system integrations, MDM |

### When to Use Each Strategy

```
START — Choosing a cross-system ID strategy
├── How many systems are being integrated?
│   ├── 2 systems
│   │   ├── Does the target ERP have native External ID?
│   │   │   ├── YES (Salesforce, NetSuite, D365)
│   │   │   │   └── Use native ExternalId/alternate key with source system ID
│   │   │   └── NO (SAP, some Oracle scenarios)
│   │   │       └── Create custom field + mapping table in middleware
│   │   └── Is this bidirectional sync?
│   │       ├── YES → Each system stores the other's ID in its External ID field
│   │       └── NO → Source ID stored in target's External ID field
│   ├── 3+ systems
│   │   └── Build a cross-reference mapping table (see schema below)
│   │       ├── In middleware/iPaaS (MuleSoft xref, Boomi cross-reference)
│   │       └── OR in a dedicated mapping database
│   └── Hub-and-spoke (MDM)
│       └── Use Oracle OrigSystemReference pattern or MDM registry
├── Do records have a natural business key?
│   ├── YES (PO numbers, invoice numbers, SKUs)
│   │   └── Use the natural key as External ID value — most readable, least fragile
│   └── NO (auto-generated IDs only)
│       └── Generate a composite key: {source_system}:{local_id} — e.g., "SF:001ABC"
└── Will records be created in multiple systems?
    ├── YES → Pre-assign IDs before any system creates the record (UUID or sequence)
    └── NO → Use source system's native ID as the External ID value in target
```

## Cross-Reference Mapping Table Design

For integrations spanning 3+ systems, a dedicated cross-reference (xref) table is the most scalable approach. This pattern is used by Oracle SOA Suite, MuleSoft, Boomi, and most enterprise middleware platforms. [src7]

### Schema Design

```sql
-- Cross-reference mapping table — one row per entity per system
CREATE TABLE integration_xref (
    xref_id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    entity_type     VARCHAR(50)  NOT NULL,  -- 'customer', 'order', 'product'
    canonical_id    VARCHAR(100) NOT NULL,  -- System-neutral ID (GUID or sequence)
    source_system   VARCHAR(30)  NOT NULL,  -- 'salesforce', 'netsuite', 'sap'
    source_id       VARCHAR(255) NOT NULL,  -- ID in the source system
    created_at      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    is_deleted      BOOLEAN      NOT NULL DEFAULT FALSE,

    -- Unique constraint: one mapping per entity type + system + source ID
    CONSTRAINT uq_xref_entity_system_id
        UNIQUE (entity_type, source_system, source_id),

    -- Index for reverse lookups (given canonical_id, find all system IDs)
    CONSTRAINT uq_xref_entity_canonical_system
        UNIQUE (entity_type, canonical_id, source_system)
);

-- Index for the most common lookup: "Given system A's ID, find system B's ID"
CREATE INDEX idx_xref_lookup
    ON integration_xref (entity_type, source_system, source_id);
```

### Lookup Pattern

```sql
-- Translate Salesforce Account ID to NetSuite Customer ID
SELECT target.source_id AS netsuite_id
FROM integration_xref source
JOIN integration_xref target
    ON source.entity_type   = target.entity_type
   AND source.canonical_id  = target.canonical_id
WHERE source.entity_type  = 'customer'
  AND source.source_system = 'salesforce'
  AND source.source_id     = '001ABC123DEF'
  AND target.source_system = 'netsuite'
  AND target.is_deleted    = FALSE;
```

### Common Value Pattern

The Oracle SOA Suite cross-reference model adds a `COMMON` column — a system-neutral canonical ID that all system-specific IDs map through. This avoids N-to-N direct mappings. [src7]

| canonical_id | salesforce | netsuite | sap | d365 |
|---|---|---|---|---|
| CM-001 | 001ABC | 12345 | BP-0001 | {guid-1} |
| CM-002 | 001DEF | 12346 | BP-0002 | {guid-2} |
| CM-003 | 001GHI | null | BP-0003 | {guid-3} |

## Cross-System Comparison

| Capability | Salesforce | NetSuite | SAP S/4HANA | Dynamics 365 | Oracle ERP Cloud |
|---|---|---|---|---|---|
| **Native External ID field** | Yes — custom field, checkbox | Yes — built-in attribute | No — use custom Z-field | No — use Alternate Keys | Yes — OrigSystemReference |
| **Upsert operation** | PATCH by ExternalId | upsert/upsertList (SOAP), eid: (REST) | No native upsert — search + conditional create | PATCH by Alternate Key | FBDI merge / REST with source key |
| **Uniqueness enforcement** | DB unique index (if Unique checked) | DB unique per record type | Fuzzy threshold (ICM, BP only) | DB unique nonclustered index | Unique per source system + ref |
| **Max external ID fields** | 7 per std object, 25 per custom | 1 per record (single value) | Unlimited custom Z-fields | 10 keys, 5 fields per key | Multiple source system refs |
| **Case sensitivity** | Case-sensitive | Case-insensitive | N/A (custom field dependent) | Case-sensitive | Case-sensitive |
| **Multi-system support** | 1 field per source system | 1 owner per record type | Custom design required | 1 key per source system | Multiple refs per record (native) |
| **Duplicate rules (advisory)** | Yes — configurable matching rules | Limited (saved search) | Yes — ICM fuzzy matching (BP) | Yes — configurable rules | Yes — CDM duplicate identification |
| **API dedup default** | OFF — opt-in via header | Always (via upsert) | ON for BP fuzzy check | OFF — opt-in via header | ON for source system refs |
| **Bulk import dedup** | External ID on Bulk API 2.0 | CSV import with externalId | Custom ABAP / middleware | DMF with alternate key | FBDI with source system key |
| **Best for** | CRM integration, multi-field matching | 2-system ERP sync | Master data (BP) dedup | Platform integrations | Hub-and-spoke MDM |

## Code Examples

### Python: Salesforce Upsert with ExternalId

```python
# Input:  Account record with external system ID
# Output: Upserted record — created (201) or updated (204)

import requests  # requests==2.32.3

def sf_upsert_by_external_id(instance_url, access_token, sobject, ext_id_field, ext_id_value, record_data):
    """
    Salesforce upsert: insert or update based on External ID field.
    Returns (status_code, record_id_or_none).
    """
    url = f"{instance_url}/services/data/v62.0/sobjects/{sobject}/{ext_id_field}/{ext_id_value}"
    headers = {
        "Authorization": f"Bearer {access_token}",
        "Content-Type": "application/json",
        # Disable duplicate rules for integration — or set to false to enable
        "Sforce-Duplicate-Rule-Header": "allowSave=true"
    }
    resp = requests.patch(url, json=record_data, headers=headers)

    if resp.status_code == 201:  # Created
        return resp.status_code, resp.json().get("id")
    elif resp.status_code == 204:  # Updated (no content)
        return resp.status_code, None  # Record existed, updated in place
    else:
        raise Exception(f"Upsert failed: {resp.status_code} — {resp.text}")

# Usage
result = sf_upsert_by_external_id(
    instance_url="https://myorg.my.salesforce.com",
    access_token="00D...",
    sobject="Account",
    ext_id_field="ERP_Customer_ID__c",  # Must be marked External ID + Unique
    ext_id_value="CUST-2026-001",
    record_data={"Name": "Acme Corp", "BillingCity": "New York"}
)
# result: (201, "001ABC...") for new, (204, None) for update
```
[src1]

### JavaScript: NetSuite Upsert with ExternalId (SuiteTalk SOAP)

```javascript
// Input:  Customer record with externalId from source system
// Output: Upserted NetSuite customer — created or updated

// Using netsuite-suitetalk (npm: netsuite-suitetalk@1.1.0)
const NetSuite = require('netsuite-suitetalk');

async function nsUpsertCustomer(externalId, customerData) {
  const client = new NetSuite({ /* TBA credentials */ });

  const customer = {
    recordType: 'customer',
    externalId: externalId,         // Source system ID — case-insensitive!
    companyName: customerData.name,
    email: customerData.email,
    subsidiary: { internalId: '1' }  // Required for OneWorld
  };

  // upsert: matches on externalId — creates if not found, updates if found
  const result = await client.upsert({ record: customer });
  // Returns: { writeResponse: { status: { isSuccess: true }, baseRef: { internalId: '12345' } } }
  return result.writeResponse;
}

// REST alternative — use eid: prefix for external ID reference
// GET    /record/v1/customer/eid:CUST-001       (retrieve by external ID)
// PATCH  /record/v1/customer/eid:CUST-001       (update by external ID)
// Note: REST upsert requires checking existence first, then POST or PATCH
```
[src2, src8]

### Python: Dynamics 365 Upsert with Alternate Key

```python
# Input:  Contact record with alternate key (email address)
# Output: Upserted D365 contact — created or updated

import requests  # requests==2.32.3

def d365_upsert_by_alternate_key(org_url, access_token, entity_set, key_field, key_value, record_data):
    """
    Dynamics 365 upsert using alternate key.
    Alternate key must be pre-defined in Dataverse entity configuration.
    """
    url = f"{org_url}/api/data/v9.2/{entity_set}({key_field}='{key_value}')"
    headers = {
        "Authorization": f"Bearer {access_token}",
        "Content-Type": "application/json",
        "OData-MaxVersion": "4.0",
        "OData-Version": "4.0",
        # To also check duplicate detection rules (optional):
        # "MSCRM.SuppressDuplicateDetection": "false"
    }
    resp = requests.patch(url, json=record_data, headers=headers)

    if resp.status_code == 204:  # Updated existing
        return "updated", None
    elif resp.status_code == 201:  # Created new
        return "created", resp.headers.get("OData-EntityId")
    else:
        raise Exception(f"Upsert failed: {resp.status_code} — {resp.text}")

# Usage — alternate key on erp_customer_id field
result = d365_upsert_by_alternate_key(
    org_url="https://myorg.crm.dynamics.com",
    access_token="eyJ...",
    entity_set="contacts",
    key_field="erp_customer_id",   # Must be defined as alternate key in Dataverse
    key_value="CUST-2026-001",     # No special chars: /,<,>,*,%,&,:,\,?,+
    record_data={"firstname": "Jane", "lastname": "Doe", "emailaddress1": "jane@acme.com"}
)
```
[src4, src6]

### cURL: SAP S/4HANA — Search Before Create (Duplicate Check)

```bash
# Input:  Business Partner name/address to check for duplicates
# Output: List of potential duplicate BPs (fuzzy match)

# Step 1: Search for existing Business Partners matching the name
curl -X GET \
  "https://my-s4hana.com/sap/opu/odata/sap/API_BUSINESS_PARTNER/A_BusinessPartner?\$filter=BusinessPartnerFullName eq 'Acme Corporation'&\$select=BusinessPartner,BusinessPartnerFullName,BusinessPartnerCategory" \
  -H "Authorization: Basic $(echo -n 'user:pass' | base64)" \
  -H "Accept: application/json"

# Step 2: If no match found, create new BP with custom Z-field for external reference
curl -X POST \
  "https://my-s4hana.com/sap/opu/odata/sap/API_BUSINESS_PARTNER/A_BusinessPartner" \
  -H "Authorization: Basic $(echo -n 'user:pass' | base64)" \
  -H "Content-Type: application/json" \
  -H "X-CSRF-Token: <fetched-token>" \
  -d '{
    "BusinessPartnerCategory": "2",
    "BusinessPartnerFullName": "Acme Corporation",
    "YY1_ExtSystemID_bus": "SF:001ABC"
  }'

# Note: SAP has no native upsert — you MUST search first, then create or update.
# The ICM fuzzy duplicate check runs server-side for BP transactions but
# does not block API calls by default — it returns warnings only.
```
[src3]

## ID Mapping Table Design Patterns

### Pattern 1: Direct Field Mapping (2 Systems)

Store the foreign system's ID directly in a custom field on each system. Simplest approach — no middleware state.

| System | Field | Stores |
|---|---|---|
| Salesforce Account | `NetSuite_ID__c` (External ID) | NetSuite internalId |
| NetSuite Customer | `externalId` | Salesforce Account ID (18-char) |

**Limitation**: Does not scale beyond 2 systems. Adding a third system requires new fields on all existing systems.

### Pattern 2: Middleware Cross-Reference Table (3+ Systems)

Central mapping table in middleware (MuleSoft Object Store, Boomi cross-reference, dedicated DB). All ID translations go through this table. See the SQL schema in the "Cross-Reference Mapping Table Design" section above.

**Lookup flow**:
1. Source event arrives with source system ID
2. Query xref: `WHERE entity_type='customer' AND source_system='salesforce' AND source_id='001ABC'`
3. Get `canonical_id` → query for target system IDs
4. If no mapping exists → this is a new record → create in target systems → register all IDs in xref table

### Pattern 3: Oracle OrigSystemReference (Hub MDM)

Oracle's native pattern stores N-to-1 mappings: multiple source system references point to a single Oracle party/entity. [src5]

| SOURCE_SYSTEM | ORIG_SYSTEM_REFERENCE | ORACLE_PARTY_ID |
|---|---|---|
| SALESFORCE | ACC-001ABC | 10001 |
| SAP | BP-0001 | 10001 |
| LEGACY_CRM | C-3003 | 10001 |

**Advantage**: Native to Oracle Fusion — no middleware mapping table needed for Oracle-centric architectures.

## Error Handling & Failure Points

### Common Error Codes

| Code/Error | System | Meaning | Cause | Resolution |
|---|---|---|---|---|
| `DUPLICATE_VALUE` | Salesforce | Unique field constraint violated | Two records have same External ID value | Merge duplicates or fix source data |
| `DUPLICATE_EXTERNAL_ID` | Salesforce | Multiple source records share same External ID | Batch upsert file has duplicate keys | Deduplicate source file pre-submission |
| 400 Bad Request | NetSuite | ExternalId collision | Attempting to set externalId to value already assigned to another record | Query existing record first, update instead |
| `0x80040237` | Dynamics 365 | Alternate key violation | Record with same key values already exists | Use upsert (PATCH) instead of create (POST) |
| `DUPLICATE_DETECTED` | Dynamics 365 | Duplicate Detection Rule triggered | `SuppressDuplicateDetection: false` header set and match found | Merge records or set header to true |

### Failure Points in Production

- **Orphaned references after merge**: When two duplicate records are merged in one ERP (e.g., Salesforce Account merge), the surviving record's External ID is kept but the losing record's ID becomes orphaned in other systems' reference tables. Fix: `Subscribe to merge events (Salesforce MergeEvent CDC) and propagate the mapping update to all connected systems.` [src1]
- **Case mismatch on NetSuite external IDs**: Source system generates `ORD-001` and `ord-001` as different records; NetSuite treats them as the same externalId. Upsert silently updates the wrong record. Fix: `Normalize all external IDs to uppercase (or lowercase) before sending to NetSuite.` [src2]
- **SAP duplicate check bypass on API**: SAP ICM fuzzy duplicate check fires in GUI (transaction BP) but may only return warnings — not blocks — when called via OData/BAPI. Integration creates duplicates that GUI users would have caught. Fix: `Implement pre-check search in middleware before BAPI create call.` [src3]
- **D365 alternate key special characters**: If an External ID value contains `/`, `<`, `>`, `*`, `%`, `&`, `:`, `\`, `?`, or `+`, the upsert PATCH request returns 400. Fix: `URL-encode values OR use a sanitized ID format (alphanumeric + hyphens only).` [src4]
- **Cascading update propagation failure**: When a natural business key changes (e.g., customer renames and gets new PO prefix), all linked systems need to update their references. If one system fails, the xref table becomes inconsistent. Fix: `Use surrogate keys (GUID/sequence) as canonical IDs — never use mutable business attributes as integration keys.` [src7]

## Anti-Patterns

### Wrong: Using Auto-Generated IDs as Integration Keys

```python
# BAD — Using Salesforce auto-generated record ID as the primary integration key
# Salesforce IDs are org-specific — they change between sandboxes,
# are meaningless to other systems, and cannot be pre-assigned.

new_order = {
    "sfdc_id": "a0B5e00000XYZ123",  # Salesforce auto-ID
    "netsuite_id": None  # Will be filled after NS creates record
}
# Problem: Race condition — if NS create fails, SFDC already has a committed record
# with no cross-reference. On retry, you create a duplicate in NetSuite.
```

### Correct: Using Stable Business Keys or Pre-Assigned GUIDs

```python
# GOOD — Pre-assign a canonical ID before creating in ANY system.
# Both systems receive the same cross-reference on first creation.

import uuid

canonical_id = str(uuid.uuid4())  # Generate once, use everywhere
sf_external_id = f"INT:{canonical_id}"

# Create in Salesforce with pre-assigned external ID
sf_upsert_by_external_id(
    sobject="Order__c",
    ext_id_field="Integration_ID__c",
    ext_id_value=sf_external_id,
    record_data={"Name": "PO-2026-142"}
)

# Create in NetSuite with SAME canonical ID
ns_upsert_customer(
    externalId=sf_external_id,
    customerData={"name": "PO-2026-142"}
)
# Both records now reference the same canonical ID — retry-safe and traceable.
```

### Wrong: Not Handling Case Sensitivity Across Systems

```javascript
// BAD — Passing External IDs without normalization across systems
// Salesforce: case-sensitive, NetSuite: case-insensitive, D365: case-sensitive

const sourceId = "Acct-ABC-123";  // From Salesforce
// NetSuite will treat "Acct-ABC-123" and "acct-abc-123" as the SAME record
// If another integration wrote "ACCT-ABC-123" to NetSuite, this upsert
// will silently update the wrong record.
```

### Correct: Normalize IDs Before Cross-System Operations

```javascript
// GOOD — Always normalize to a consistent case before using as External ID

function normalizeExternalId(systemId, targetSystem) {
  // NetSuite is case-insensitive — normalize to uppercase
  if (targetSystem === 'netsuite') {
    return systemId.toUpperCase();
  }
  // All other systems — preserve original case but strip whitespace
  return systemId.trim();
}

const sourceId = "Acct-ABC-123";
const nsExternalId = normalizeExternalId(sourceId, 'netsuite');
// Result: "ACCT-ABC-123" — consistent, collision-proof
```

### Wrong: Single ExternalId Field for Multiple Source Systems

```python
# BAD — Reusing NetSuite's single externalId for multiple integration partners
# System A writes externalId = "SYS_A:001"
# System B overwrites externalId = "SYS_B:XYZ"
# System A's reference is now broken — lookups return no match.
```

### Correct: Reserve ExternalId for Primary Integration, Use Custom Fields for Others

```python
# GOOD — Primary system uses externalId, secondary systems use custom fields.
# Or: use a middleware cross-reference table and don't rely on NetSuite externalId
# for multi-system lookups.

netsuite_record = {
    "externalId": "SALESFORCE:001ABC",        # Primary integration partner
    "custentity_sap_bp_id": "BP-0001",         # Custom field for SAP reference
    "custentity_legacy_crm_id": "C-3003"       # Custom field for legacy system
}
```

## Common Pitfalls

- **Not handling partial success in batch upserts**: Salesforce Bulk API 2.0 processes records independently — some may succeed while others fail on duplicate External ID. If you retry the entire batch, you double-create the records that originally succeeded. Fix: `Parse the result file, retry only failed records, and use the External ID upsert (not insert) so successful records are safely updated.` [src1]
- **Assuming External ID uniqueness is global in NetSuite**: External IDs are unique per record type, but Customer and Employee share an ID space. Creating a Customer with externalId `EMP-001` blocks creating an Employee with the same externalId. Fix: `Prefix external IDs with the record type: 'CUST:EMP-001' vs 'EMP:EMP-001'.` [src2]
- **Confusing D365 Duplicate Detection Rules with enforcement**: Duplicate Detection Rules are advisory — users can override them, and they are OFF by default for API calls. Alternate keys are absolute enforcement. If you need guaranteed uniqueness for integration, use alternate keys, not duplicate detection rules. Fix: `Define an alternate key for the integration key field; use duplicate detection rules only for UI-based data entry guidance.` [src4]
- **Forgetting to register source systems in Oracle ERP Cloud**: Attempting to write OrigSystemReference values for an unregistered source system fails silently or throws an obscure error. Fix: `Register all source systems in Oracle Fusion Setup before running any integration that writes source system references.` [src5]
- **Using mutable business attributes as natural keys**: Company names change, email addresses change, even PO number formats change during ERP migrations. Fix: `Use immutable identifiers (auto-generated IDs, GUIDs) as integration keys. Store natural keys as searchable attributes, not as the integration key itself.` [src7]

## Diagnostic Commands

```bash
# Salesforce: Check External ID field configuration
curl -s "$SF_INSTANCE/services/data/v62.0/sobjects/Account/describe" \
  -H "Authorization: Bearer $SF_TOKEN" | \
  jq '.fields[] | select(.externalId==true) | {name, label, unique, type}'

# Salesforce: Find duplicate External ID values on an object
# Use SOQL: SELECT ERP_Customer_ID__c, COUNT(Id) FROM Account GROUP BY ERP_Customer_ID__c HAVING COUNT(Id) > 1

# NetSuite: Retrieve record by External ID (REST)
curl -s "https://ACCOUNT_ID.suitetalk.api.netsuite.com/services/rest/record/v1/customer/eid:CUST-001" \
  -H "Authorization: OAuth ..." \
  -H "Content-Type: application/json"

# Dynamics 365: List alternate keys on an entity
curl -s "$D365_URL/api/data/v9.2/EntityDefinitions(LogicalName='contact')/Keys" \
  -H "Authorization: Bearer $D365_TOKEN" \
  -H "OData-Version: 4.0"

# Cross-reference table: Find orphaned mappings (records deleted from source but still in xref)
# SELECT x.* FROM integration_xref x
# LEFT JOIN salesforce_accounts sf ON x.source_id = sf.id AND x.source_system = 'salesforce'
# WHERE sf.id IS NULL AND x.source_system = 'salesforce' AND x.is_deleted = FALSE;
```

## When to Use / When Not to Use

| Use When | Don't Use When | Use Instead |
|---|---|---|
| Building any multi-system integration that creates or syncs records | One-time data migration (load and done) | Pre-migration cleansing + dedup tools (Informatica, Talend) |
| Need deterministic insert-or-update behavior across systems | Single-system CRUD with no external references | Native platform duplicate rules only |
| Designing ID strategy for 3+ system integration | Systems only share read-only data (no writes) | Simple API queries — no dedup needed |
| Implementing real-time bidirectional sync | Event-driven read replicas with no writeback | Change Data Capture without dedup |
| MDM hub needs to track record provenance across sources | Already using a commercial MDM product with built-in xref | Vendor MDM (Informatica MDM, Reltio, Semarchy) |

## Important Caveats

- Duplicate detection capabilities vary dramatically between ERP editions — e.g., Salesforce Professional edition has limited External ID support; NetSuite OneWorld has different subsidiary-scoping rules for external IDs [src1, src2]
- SAP S/4HANA Cloud and On-Premise have different duplicate check capabilities — Cloud has restricted customization options for ICM threshold configuration [src3]
- This card covers API-level duplicate detection; it does not cover UI-based data quality tools (Salesforce Data.com, D365 Data Quality add-on) which are separate product features
- Dynamics 365 alternate key indexes may take minutes to hours to activate after creation — test in a non-production environment before relying on them for integration [src4]
- Oracle ERP Cloud's OrigSystemReference model requires Oracle Customer Data Management (CDM) module for full duplicate identification capabilities beyond basic source system matching [src5]
- Cross-reference mapping table performance degrades with millions of mappings if not properly indexed — partition by entity_type for large-scale deployments [src7]

## Related Units

- [Idempotency in ERP Integrations](/business/erp-integration/idempotency-erp-integrations/2026)
- [Master Data Management for ERP](/business/erp-integration/master-data-management-erp/2026)
- [Field Mapping and Data Type Comparison](/business/erp-integration/field-mapping-data-type-comparison/2026)
- [Salesforce-NetSuite Integration](/business/erp-integration/salesforce-netsuite-integration/2026)
- [Salesforce-SAP Integration](/business/erp-integration/salesforce-sap-integration/2026)