Dataverse alternate keys

Every Dataverse record has a GUID as its primary identifier — a 128-bit unique value generated automatically. GUIDs are great for internal references but terrible for external integration: external systems rarely know your GUIDs but they do know business identifiers like email addresses, tax IDs, employee numbers, item codes. Alternate keys in Dataverse let records be uniquely identified by these business-meaningful values, dramatically simplifying integration and lookup scenarios.

The model. An alternate key is a defined uniqueness constraint on one or more columns of a table. Once configured:

• Inserts / updates that violate the uniqueness fail with a clear error.
• The Web API supports retrieval by the alternate key, not just by GUID.
• Lookups via Upsert operations use the alternate key for matching.

A table can have multiple alternate keys — each enforcing uniqueness on a different column combination.

Example — Account by Tax ID. Define an alternate key on the Account table over the Tax ID column. The constraint:

• No two accounts can have the same Tax ID.
• An integration receiving Tax ID "DK12345678" can query /api/data/v9.2/accounts(taxid='DK12345678') to get the account directly.
• An Upsert operation with Tax ID as the match key inserts a new account if none exists, updates if one does.

Common alternate key patterns.

• Contact by email — uniqueness on Email Address. Standard for integration with email-based identification.
• Account by Tax ID / EIN / VAT number — uniqueness on the country-specific tax identifier.
• Product by Product Number — when integrating with an ERP that uses product numbers.
• Lead by external system ID — when leads come from a marketing automation platform with its own IDs.
• Opportunity by Quote Number — when external systems reference opportunities by a business quote number.
• Combined keys — e.g. uniqueness on (Customer + Reference Number) for cases where neither alone is unique.

Setting up an alternate key.

1. From the table's settings in the maker portal, add an alternate key.
2. Name it.
3. Choose the column(s) included.
4. Activate.
5. Dataverse runs a one-time job to validate existing data and create the index.

If existing data violates the uniqueness, the activation fails with a list of conflicts to resolve first.

Integration benefits.

• Cleaner Upsert — external systems insert / update by business key; no need to query for GUID first.
• Reduced GUID exposure — integrations don't need to track Dataverse GUIDs in their own systems.
• Match-based deduplication — alternate keys prevent duplicate creation at integration time.

Performance. Alternate keys create unique indexes underneath; queries by alternate key are fast. The cost is index storage and a small write overhead on every insert / update to maintain the index.

Limits.

• Activation can fail if existing data has duplicates. Resolve duplicates first or activate against a clean migration.
• Some column types are not supported (e.g. memo, multi-line text). Verify per type.
• GUID lookup is still required for some scenarios — the Web API URL format for alternate-key lookup has specific encoding requirements.
• No partial indexes — uniqueness applies across the whole table; can't say "unique only when Active = Yes".

Status field considerations. A common challenge: customers want "unique by email, but only for active contacts" — deactivated duplicates are tolerable. Alternate keys don't support filtered uniqueness. Workarounds:

• Use the email + status code as a combined key (but this creates an odd uniqueness shape).
• Implement uniqueness logic in a plug-in instead of an alternate key.
• Accept that deactivated records still consume the unique value.

Common pitfalls.

• Activation against dirty data — failure surfaces all duplicates; resolution is data-cleanup work.
• Performance over-confidence — alternate keys help specific queries but don't transform the table's general query performance.
• Schema lock-in — once integrations depend on a specific alternate key, removing it is invasive.
• Multi-language collation — alternate keys on text fields can have collation differences across regions; verify behaviour.

Operational discipline. Identify alternate-key candidates during schema design. Activate before significant data accumulates. Document the alternate keys in the integration documentation. Treat them as part of the Dataverse contract with external systems.

When to use them. Use alternate keys when:

• An external system has a stable business identifier you'd otherwise have to look up.
• Upsert operations need a non-GUID matching field.
• Uniqueness is a genuine business constraint, not just a convenience.

Don't use alternate keys for every column that "should" be unique in some sense — only where the business constraint is real and the integration value is clear.