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 max 7 External ID fields/std object; NetSuite 1 externalId value/record; D365 max 5 fields per alternate key with 10 keys per entity.
• Watch out for: NetSuite external IDs are case-insensitive — ORD-001 and ord-001 collide silently, causing 400 errors in production.
• 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) that supports multiple source system registrations per record.

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.

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.

Limits: Max 7 External ID fields per standard object, 25 per custom object. External ID fields count toward the custom field limit.

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.

Case sensitivity: External IDs in NetSuite are case-insensitive. CUST-001 and cust-001 are treated as the same value.

REST API: Use eid: prefix in URLs — 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.

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): 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.

Other entities: No built-in duplicate check. Integration teams must either create custom Z-fields (e.g., ZEXT_SYSTEM_ID) with custom ABAP duplicate checks, or implement deduplication in middleware.

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, then conditionally create or update.

Microsoft Dynamics 365: Alternate Keys + Duplicate Detection Rules

Dynamics 365 (Dataverse) provides two distinct mechanisms. [src4]

Alternate Keys: Database-level unique indexes. Once defined, they enforce absolute uniqueness. Alternate keys enable upsert via the Web API: PATCH /api/data/v9.2/{entityset}({keyfield}='{value}'). [src6]

Duplicate Detection Rules: Application-level matching with configurable criteria. By default, these do NOT apply to Web API/SDK operations. To enable programmatically, set MSCRM.SuppressDuplicateDetection: false in the request header.

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.

Oracle ERP Cloud: OrigSystemReference + Source System Registration

Oracle Fusion Cloud uses a source system reference model. Records 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, Oracle allows multiple source system references per record. A customer record can simultaneously have references from SAP, Salesforce, and a legacy system.

Source system registration: Each source system must be explicitly registered in Oracle Fusion before it can write source system references.

Merge behavior: When importing data with Allow Multiple Source System References enabled, Oracle can merge duplicate source records into a single Oracle record.

Natural Key Design Strategies

GUID vs Business Key vs Surrogate Key

ID Strategy Decision Tree

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
│   │       ├── 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
│   └── NO (auto-generated IDs only)
│       └── Generate a composite key: {source_system}:{local_id}
└── Will records be created in multiple systems?
    ├── YES → Pre-assign IDs before any system creates the record (UUID)
    └── NO → Use source system's native ID as the External ID 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

-- 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,

    CONSTRAINT uq_xref_entity_system_id
        UNIQUE (entity_type, source_system, source_id),
    CONSTRAINT uq_xref_entity_canonical_system
        UNIQUE (entity_type, canonical_id, source_system)
);

CREATE INDEX idx_xref_lookup
    ON integration_xref (entity_type, source_system, source_id);

Lookup Pattern

-- 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

Cross-System Comparison

Code Examples

Python: Salesforce Upsert with ExternalId

# 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):
    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",
        "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
    else:
        raise Exception(f"Upsert failed: {resp.status_code} — {resp.text}")

[src1]

JavaScript: NetSuite Upsert with ExternalId (SuiteTalk)

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

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

// REST alternative — use eid: prefix for external ID reference
// GET    /record/v1/customer/eid:CUST-001       (retrieve)
// PATCH  /record/v1/customer/eid:CUST-001       (update)

[src2, src8]

Python: Dynamics 365 Upsert with Alternate Key

# Input:  Contact record with alternate key (integration ID)
# Output: Upserted D365 contact — created or updated

def d365_upsert_by_alternate_key(org_url, access_token, entity_set, key_field, key_value, record_data):
    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"
    }
    resp = requests.patch(url, json=record_data, headers=headers)
    if resp.status_code == 204:    return "updated", None
    elif resp.status_code == 201:  return "created", resp.headers.get("OData-EntityId")
    else: raise Exception(f"Upsert failed: {resp.status_code} — {resp.text}")

[src4, src6]

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

# 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'" \
  -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 "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.

[src3]

Error Handling & Failure Points

Common Error Codes

Failure Points in Production

• Orphaned references after merge: When two duplicate records are merged in one ERP, the surviving record's External ID is kept but the losing record's ID becomes orphaned in other systems. Fix: Subscribe to merge events 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. Fix: Normalize all external IDs to uppercase before sending to NetSuite. [src2]
• SAP duplicate check bypass on API: ICM fuzzy check fires in GUI but may only return warnings via OData/BAPI. Fix: Implement pre-check search in middleware before BAPI create call. [src3]
• D365 alternate key special characters: If an External ID value contains /,<,>,*,%,&,:,\,?,+, the upsert 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, all linked systems need updates. If one 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

# 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  # Race condition if NS create fails
}

Correct: Using Stable Business Keys or Pre-Assigned GUIDs

# GOOD — Pre-assign a canonical ID before creating in ANY system.
import uuid
canonical_id = str(uuid.uuid4())
sf_external_id = f"INT:{canonical_id}"
# Both systems receive the same cross-reference on first creation.
# Retry-safe and traceable.

Wrong: Not Handling Case Sensitivity Across Systems

// BAD — Passing External IDs without normalization
const sourceId = "Acct-ABC-123";  // From Salesforce
// NetSuite treats "Acct-ABC-123" and "acct-abc-123" as the SAME record

Correct: Normalize IDs Before Cross-System Operations

// GOOD — Always normalize to consistent case before using as External ID
function normalizeExternalId(systemId, targetSystem) {
  if (targetSystem === 'netsuite') return systemId.toUpperCase();
  return systemId.trim();
}

Wrong: Single ExternalId Field for Multiple Source Systems

# 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.

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

# GOOD — Primary system uses externalId, others use custom fields
netsuite_record = {
    "externalId": "SALESFORCE:001ABC",          # Primary partner
    "custentity_sap_bp_id": "BP-0001",           # Custom field for SAP
    "custentity_legacy_crm_id": "C-3003"         # Custom field for legacy
}

Common Pitfalls

• Not handling partial success in batch upserts: Salesforce Bulk API 2.0 processes records independently — some may succeed while others fail. If you retry the entire batch, you double-create the records that originally succeeded. Fix: Parse the result file, retry only failed records using External ID upsert. [src1]
• Assuming External ID uniqueness is global in NetSuite: External IDs are unique per record type, but Customer and Employee share an ID space. 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 and OFF by default for API calls. Alternate keys are absolute enforcement. 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. Fix: Register all source systems in Oracle Fusion Setup before running integrations. [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. [src7]

Diagnostic Commands

# 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}'

# 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 ..."

# 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
# SELECT x.* FROM integration_xref x
# LEFT JOIN salesforce_accounts sf ON x.source_id = sf.id
# WHERE sf.id IS NULL AND x.source_system = 'salesforce' AND x.is_deleted = FALSE;

When to Use / When Not to Use

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
• SAP S/4HANA Cloud and On-Premise have different duplicate check capabilities — Cloud has restricted customization options for ICM threshold configuration
• 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 non-production before relying on them for integration
• Oracle ERP Cloud's OrigSystemReference model requires Oracle Customer Data Management (CDM) module for full duplicate identification beyond basic source system matching
• Cross-reference mapping table performance degrades with millions of mappings if not properly indexed — partition by entity_type for large-scale deployments