Alternate keys

What it does

An alternate key lets you identify a row by one or more business columns instead of its GUID. It's the mechanism that makes upsert work against an external system's identifiers, and it enforces uniqueness on those columns as a side effect.

Key facts

A table can have up to ten alternate key definitions.
Only these column types are valid in a key: decimal, whole number, single line of text, date time, lookup and choice.
Columns with column-level security applied can't be used in a key.
The key must satisfy SQL index constraints — 900 bytes per key and 16 columns per key. Exceed either and you get an error on save.
The key isn't usable the moment you save it. A background system job builds the supporting index, and the status moves through Pending, In Progress, Active or Failed.
If index creation fails, you fix the underlying data and reactivate the key with ReactivateEntityKey. Deleting the key mid-build cancels the job and drops the index.
If key column data contains /, <, >, *, %, &, :, \, ? or +, then GET, update and upsert against that key don't work. Uniqueness still holds — only the URL-addressable operations break.
Alternate keys aren't supported on virtual tables (Dataverse can't enforce uniqueness on someone else's data) or on elastic tables.

When to use / skip

Define an alternate key whenever an external system is the source of truth for identity and can't store Dataverse GUIDs — an ERP customer number, a payroll ID, a scanned reference. It turns a fragile retrieve-then-branch integration into a single upsert. Skip it when you only want uniqueness enforced for data-quality reasons on a table with millions of rows and no integration need; you're paying for an index and a fragile creation job to get a validation rule. And don't add one on a column whose values people edit casually, because the key is only as stable as the data behind it.

Configuration decisions

Which business columns genuinely identify a row, and whether one column is enough or you need a composite.
Whether those columns are stable. A key on something users retype is a key that breaks.
Whether the source data can contain the characters that break GET and PATCH, and whether you clean it or pick different columns.
Whether the key is for integration (design for URL-safety) or purely for uniqueness (constraints are looser).
The order you create keys on a large table, since each one triggers an index build.

Gotchas

Index creation on a table that already holds millions of rows takes a long time and can fail outright on duplicate data. You find out asynchronously, and the key silently sits inactive while your integration fails.
Duplicates already in the table are the usual failure cause. Deduplicate before you create the key, not after the job fails.
Solution import creates the key in the target the same way — as an async job. A pipeline that imports and then immediately runs an upsert can race the index build.
The forbidden-character list is the one that catches integration teams. An account number with a / in it looks fine, saves fine, and then can't be retrieved by key.
Enabling column security on a column that's part of an existing key isn't compatible with the key definition. Decide which one you want.

Consultant notes

Profile the source data for duplicates and dodgy characters before you define anything. Ten minutes of SQL saves a failed deployment.
Build alternate keys as an early, standalone deployment step so index creation completes well before the integration goes anywhere near the environment.
Add the key status check to your go-live checklist. "Active" is the only acceptable value, and nothing in the UI shouts at you if it isn't.
Explain to the client that the key enforces uniqueness across the whole table, including rows the integration didn't create. Manual data entry can break the integration from a direction nobody expects.

Worth revisiting if the ten-key limit changes or key creation ever reports status somewhere more visible than the API.