Alternate keys in Dataverse (incl. composite keys for data imports)

Source: Claude.ai chat (original: enabling-alternate-key-fields-on-tables__257e414f.md) Added: 2026-10-02

Summary

• Alternate keys are defined at the table level, not on columns — a key record references one or more columns. Max 5 keys per table, up to 16 columns per key, total index size < 900 bytes.
• Key creation is async — a system job builds the backing SQL unique index. If duplicates exist in the data, the key lands in Failed state and must be cleaned + recreated.
• Supported column types: Single Line of Text, Whole Number, Decimal, Date/Time, Choice, Lookup (non-polymorphic), Unique Identifier. Unsupported: Multiline Text, Multi-select Choice, Currency, Floating Point, Customer/polymorphic Lookups, File/Image, and any Calculated / Rollup / Formula column.

Key takeaways

• Composite keys: for "match on email AND org code" style imports, create one key record containing both columns — not two separate keys. Column order doesn't affect matching, only the index.
• Natural-key URL syntax: GET /api/data/v9.2/contacts(emailaddress1='x@y.z',customer_legacyorgcode='CODE-042'). Same syntax works for PATCH → upsert-by-natural-key without a pre-lookup.
• KingswaySoft integration: on Dataverse Destination, set Action = Upsert and choose the alternate key as matching criteria — eliminates the legacy-ID → GUID lookup component + staging round-trip. The component reads keys from metadata; refresh metadata cache if a new key doesn't appear.
• Nulls break upsert idempotency (critical gotcha): NULL = NULL is UNKNOWN in SQL three-valued logic. A row with null in a key column is physically indexed but unreachable by key lookup → every rerun creates a new duplicate because the "not found" branch of Upsert fires. The load reports success and only duplicates are the symptom. Fix: route null-bearing rows down a second pipeline that keys on a different column (e.g. a legacy ID), or filter them out entirely. Avoid "synthetic placeholder" values that pollute data and risk real transmission.
• Empty strings → null: Dataverse normalises '' on text columns to null on write in most paths; same unreachable case. KingswaySoft has a setting for "blanks as null vs omitted" — the two Upsert behaviours differ.
• Case-insensitive matching: ABC and abc collide. Trailing whitespace is significant in Web API URLs but often not in SQL — TRIM() in staging.
• Keys can't be edited after creation — delete and recreate.

Learnings / how to apply

• "Alternate key design + null handling" is a mandatory section in any Praxisis data-migration playbook — this exact trap costs weeks when caught in UAT.
• Pre-flight dedupe query: GROUP BY <key columns> HAVING COUNT(*) > 1 as a mandatory step before enabling a key.
• Bulk upserts via alternate key still fire plugins and can mass-trigger live journeys — add CIJ-side guards before any archive run.
• Record "KingswaySoft config: match-by-alternate-key" as the preferred D365 import pattern in the Sustain Operating Model (removes an entire staging layer).

Bonus (related in same chat): Campaign + Marketing List in Customer Insights

• Neither is part of the real-time journey engine. Both are legacy CE tables, kept because Sales still uses them. Real-time journeys live on msdynmkt_journey and have no relationship to the campaign table.
• Campaign = planning + cost-tracking container; its only execution is Quick Campaign (bulk-generates activity records; a Sales workflow, not a marketing send).
• Marketing List = static or dynamic membership of accounts / contacts / leads (one type per list, never mixed). Still earns a place as a manual curation surface for non-technical staff (board members, specific cohorts) that would be awkward to express as a real-time segment.
• CASL/consent trap: Quick Campaign checks the classic donotbulkemail / donotsendmm flags; real-time journeys check contact point consent (msdynmkt_contactpointconsent*). A staff member firing a quick campaign off a marketing list transacts on a consent model that the real-time implementation doesn't govern — plausible route to a non-compliant send with no audit trail. Mitigation: restrict prvCreate on list to a small group OR remove Marketing Lists + Quick Campaign from the CIJ app sitemap entirely.
• Outbound marketing retired mid-2025 → the surviving route for lists in journeys is listmember traversal in a real-time segment.

---