Dataverse Alternate Keys: Stop Duplicate Records for Good

Every row Dataverse stores gets a GUID the instant it’s created — a long, system-generated code that works perfectly for Dataverse’s own bookkeeping and terribly for anyone else. That mismatch is exactly why Dataverse alternate keys exist: an external system feeding data in has never heard of that GUID, so without a way to look a row up by something it actually recognizes, the same order, contact, or product can end up saved two, three, five times over.

This post works through a single, original scenario end to end — a multi-store order sync using a two-column composite key — and shows, concretely, which combinations get matched, which get rejected outright, and which one dangerous case slips past the key without so much as an error.

Why GUID-Only Lookups Create Duplicate Records

Every row Dataverse stores gets a system-generated unique identifier the moment it’s created — a long, automatically assigned code called a GUID (Globally Unique Identifier). Think of it as Dataverse’s own internal serial number for that specific row, invisible to anyone outside the platform. That GUID is how Dataverse recognizes “this is the same record I saw before” during an update.

Here’s the catch. An external system — an order-management platform, an HR system, whatever is feeding data in — has never heard of that GUID. It has its own identifier for the same real-world thing: an order number, an employee ID, whatever it happens to use. When an integration only knows how to look a Dataverse row up by GUID, and it doesn’t have that GUID stored anywhere on its own side, it has exactly one option every time it needs to send that order over: create a new row. Send the same order twice — a retry after a timeout, a re-run of a nightly sync, a webhook that fires more than once — and Dataverse dutifully creates it twice. Three times, three rows.

This is the mechanism behind almost every “why do I have five copies of the same order” support ticket you’ll ever see. The integration was never wrong about the data — it just never had a usable question to ask (“does a row for this order already exist?”) using an identifier it actually possessed.

Say Store ST-04 places order ORD-1001, and the order-management system sends it to Dataverse twice — once when the order is placed, and again a minute later because its own retry logic fired after a slow response. Without an alternate key, a GUID-only integration has to create a row both times, because it has no GUID to check against yet:

That second row is the duplicate. Nothing was misconfigured — the integration simply never had a usable question to ask before creating.

Alternate keys are how you give it that question to ask. An alternate key is a rule you define on a Dataverse table saying: for this table, one or more business columns — not just the GUID — uniquely identify a row. Once defined, an integration can look a row up, update it, or create it, using the values it already has (in this post’s scenario, Order Number and Store Code together) instead of a GUID it was never given. Later in this post, you’ll see this exact two-call scenario again, this time with the key in place, so you can compare the table state directly against what you just read here.

Defining an Alternate Key: Valid Columns and Platform Limits

Not every column can be used as an alternate key. Dataverse restricts it to six column types: Decimal, Whole Number (Integer), Single Line of Text, Date and Time, Lookup (a reference field pointing at a row in another table), and Choice (a fixed dropdown of options, sometimes called an option set). A column with field-level security turned on can never be used as a key, regardless of its type — the platform simply won’t allow it.

There are also hard structural limits worth knowing before you design one:

• A single table can have up to 10 alternate key definitions.
• A key’s total size can’t exceed 900 bytes, and it can’t span more than 16 columns — both are SQL-index-level constraints Dataverse enforces at creation time. If a proposed key would violate either, Dataverse rejects it with an error rather than silently truncating anything.

For the Order table in this post’s scenario, the key spans two columns — Order Number and Store Code, both Single Line of Text fields — comfortably within every one of those limits. A composite key like this is exactly the case those limits exist for: nothing stops you from combining several columns when uniqueness only holds for the combination, as long as the total size and column count stay inside the platform’s bounds. The scenario itself explains why a composite key is worth the extra step here: this is a multi-store chain where every location numbers its own orders independently, so ORD-1001 can legitimately exist at more than one store at the same time. Neither Order Number nor Store Code is unique on its own — only the combination identifies one real order.

Alternate Keys vs. Duplicate Detection Rules

It’s worth being precise about what an alternate key is not, because Dataverse has a second, unrelated feature that sounds like it solves the same problem: duplicate detection rules. A duplicate detection rule is a fuzzy-matching feature — it looks for records that are probably the same (similar names, similar phone numbers) and flags them for a human to review. It doesn’t enforce anything by itself; it warns.

An alternate key is the opposite kind of tool. It enforces exact, indexed uniqueness on the column(s) you define — Dataverse won’t let two rows share the same key values once that key is active, full stop, no review step. It’s also what a create-or-update action, covered further down, actually uses to look a row up.

The two aren’t mutually exclusive, and Microsoft’s own documentation shows a concrete case of them working together: in Dynamics 365 Customer Insights – Journeys’ data-import process specifically, when both an alternate key and a duplicate-detection rule are active, the alternate key is checked first — it updates the matching row, or notes that no key match exists — and duplicate detection is then applied to whatever wasn’t resolved by the key. That’s documented for that particular import feature, not as a universal rule for every Dataverse import path, but it illustrates a pattern worth keeping in mind: treat the alternate key as the deterministic first pass, and duplicate detection as a softer safety net behind it for whatever the key doesn’t cover.

The Pending-to-Active Gotcha

Here’s something that catches people the first time: you create an alternate key, immediately go try to use it, and Dataverse rejects your request. That’s not a bug — it’s the key doing exactly what it’s supposed to, just not yet.

Creating an alternate key kicks off a background job that builds an index to actually support the uniqueness check. Until that job finishes, the key isn’t enforcing anything. You can watch its status move through four states: Pending, In Progress, Active, and — if something goes wrong — Failed. Only once it reaches Active will Dataverse actually use it to look up, update, or reject duplicate rows.

Defining the Key and Putting It to Work

How to define the key

The most direct path, and the one this post uses, is the Power Apps maker portal. Concretely, for the Order table in this scenario:

1. Open make.powerapps.com and go to Tables.
2. Open the Order table.
3. In the Schema area on the left, select Keys.
4. Select New key, give it a name — for example, Order Number + Store Code Key — and pick the columns it’s built on: Order Number and Store Code, since uniqueness only holds for that combination, not for either column alone.
5. Save. Dataverse immediately starts the background indexing job described above — the key’s status shows Pending, then In Progress.
6. Reopen the Keys list after a short wait and confirm the status reads Active before relying on it from any integration.

Solution explorer offers a more advanced path with extra options, like tracking the underlying system job by name — useful if you’re troubleshooting a key that’s stuck in Pending — but the maker portal steps above cover what almost every reader needs.

Two different actions, two different outcomes

There are two different ways an integration can send a record to Dataverse, and they behave very differently once an alternate key is involved:

• A plain “insert a new record” action. This always tries to create a brand-new row. If the key column’s value would collide with a row that already exists, Dataverse doesn’t silently allow it and doesn’t silently skip it either — it refuses the request outright.
• An “insert-or-update” action — commonly called an upsert, shorthand for update-or-insert. This single action means “update the matching row if one exists, or create a new one if it doesn’t,” decided automatically by looking the row up via the key. This is the action integrations should use whenever the same record might legitimately be sent more than once, which is exactly the order-resync situation from earlier in this post.

Both actions exist as options in essentially every way of talking to Dataverse — custom code, a low-code flow, an import tool. The two behaviors above are a property of Dataverse itself, not of any particular tool.

Seeing it work: the same scenario, key active

Earlier, you saw what happens without a key: the same order synced twice using a plain insert, and Dataverse created two separate rows because it had no way to recognize the second call as a repeat. Here’s the identical scenario again — the order-management system sends ORD-1001 / ST-04, then sends it again a minute later — but now with the Order Number + Store Code alternate key Active and the integration using the insert-or-update action instead of a plain insert:

That’s the entire behavior change an alternate key buys you: the second call, which used to produce a duplicate, now safely folds into the first row. Nothing about the integration’s retry logic had to change — it was always sending the same “here’s this order” message either way; only Dataverse’s ability to recognize a repeat changed.

The animation below builds this composite key’s behavior live, one sync attempt at a time — watch it play through, no clicking required. Click the box to expand it fullscreen if you’d like a closer look.

What a composite key actually compares — what gets rejected, and what silently doesn't

A two-column key raises a question a single-column key never does: what counts as "the same" when there are two values to check? The honest answer — the full combination has to match, not just one of the two columns — is easiest to see through eight concrete attempts against the same Order table with the Order Number + Store Code key active. The first four use the insert-or-update action; the next two switch to a plain insert, to show what happens when that action — rather than insert-or-update — runs into a match; the last two leave the Store Code column empty, to show the one case where a real duplicate slips through with no error at all.

The pattern across the first six: Dataverse only calls two rows "the same" when every column in the key matches, not when just one of them happens to repeat. Rows 2 and 4 each share exactly one value with row 1 (the Order Number for row 2, the Store Code for row 4), and neither is treated as a duplicate, because a composite key isn't "either column matches" — it's "all of them do." A single-column key would have gotten row 2 wrong here: ORD-1001 alone would have looked like a repeat, and it isn't. Rows 5 and 6 show the flip side: the action you choose decides what happens on an actual match — insert-or-update resolves it silently by updating (row 3), a plain insert refuses it loudly (row 5) — while a non-match always just creates, regardless of which action you used (row 6).

One thing worth being precise about: what row 5 "rejects" is the exact combination, not the existing row's other data. Dataverse doesn't touch row 1's other columns when it refuses row 5 — the row from #1/#3 is left exactly as it was; the rejection only stops the new, colliding insert from happening.

Rows 7 and 8 are the outlier, and deliberately so: they're the only pair in this table where two rows end up with an identical Order Number and, on the surface, an identical Store Code — yet nothing rejects the second one, because an empty value never participates in the uniqueness check to begin with. That's a pitfall in its own right, covered in full further down — this is what it actually looks like when it happens.

It's also worth knowing about a pitfall that doesn't show up in this table at all: a repeated value in an unrelated column, like two rows sharing the same customer name, is never checked. Uniqueness only ever applies to the columns the key is actually built on — a different column repeating, no matter how suspicious it looks, isn't something Dataverse will ever reject on its own.

A row's key value can't be silently changed

One more practical wrinkle, worth flagging because it's easy to get backwards: when an insert-or-update action uses an alternate key to find a record, whatever other value it might also be carrying for that same key column gets ignored the moment an existing row is matched.

Choosing — and Avoiding — the Wrong Key Columns

Picking the column(s) a key is built on is where a lot of the real-world pain either gets avoided or gets baked in permanently. Three things to check before you commit — and with a composite key like this post's Order Number + Store Code, they apply to each column in the combination, not just one.

Watch for characters that quietly break the key

If the data in your key column can ever contain any of / < > * % & : \ ? + — the exact list varies slightly by documentation page, and some pages also call out #, but these are consistently reserved, structurally significant characters — looking a row up, updating it, or inserting-or-updating it by that key stops working for that specific value. It happens more often than you'd expect: a product SKU with slashes in it, an order reference a partner system formats with an ampersand. If uniqueness alone is all you need, the key still works fine — it only bites you the moment you also try to use it to look a row up or target it directly.

Never let a key column go empty

A NULL value in any one of an alternate key's columns is enough to break uniqueness enforcement for that row — Dataverse won't catch two rows that both have a NULL there, even if every other column in the combination matches. With this post's two-column key, that means a missing Store Code is just as dangerous as a missing Order Number, exactly what you saw play out in rows 7 and 8 above. If your source system sometimes fails to send an identifier, you haven't picked a bad column — you've picked a column that can silently stop protecting you the moment the source misbehaves.

Don't reach for something convenient but unstable

An email address is the classic example: it looks unique, until someone changes it, or until two employees temporarily share a mailbox, or until a test account and a real account happen to collide. The column you pick has to be something the source system itself guarantees is both stable and unique for that record's lifetime — an order number, an employee ID, a SKU issued once and never reused. If the source system can't make that guarantee, no amount of clever Dataverse configuration will make it a safe key.

One more limit worth a single line, not a whole section: alternate keys don't work on virtual tables — tables whose data actually lives in another system — because Dataverse can't enforce uniqueness on data it doesn't itself store. This whole approach assumes you're working with a standard Dataverse table, like the Order table in this post.

Key Takeaways

• An alternate key lets Dataverse recognize a row by business values instead of only its internal GUID — without one, retries and resyncs create duplicates.
• A key can be composite. Uniqueness applies to the whole combination of columns, not to any single column alone — up to 16 columns and 900 bytes total.
• A brand-new key isn't enforced until its status reaches Active. Using it earlier fails with a 400 error, not a permissions problem.
• Insert-or-update (upsert) resolves a match by updating; a plain insert on the same match is rejected outright. Choose the action based on whether the same record might legitimately be sent more than once.
• Leaving any key column empty silently disables uniqueness checking for that row — the single most dangerous pitfall, because nothing errors and nothing warns you.
• Alternate keys and duplicate detection rules are different tools: exact, enforced matching versus fuzzy, human-reviewed warnings. They can be combined.

Conclusion

A GUID-only integration doesn't fail loudly when it creates a duplicate — it just quietly does its job twice. An alternate key fixes that by giving Dataverse a business-meaningful question to ask before it creates anything: does a row with these values already exist? Get the column choice right, respect the Pending-to-Active delay, and pick insert-or-update over a plain insert wherever a resync is possible, and the whole class of "why are there five copies of this order" tickets simply stops happening. Get the column choice wrong — especially by letting one column in a composite key go empty — and the key will protect you right up until the moment it doesn't, without ever telling you it stopped.

A key is only as strong as its emptiest column — leave one blank, and Dataverse will duplicate right past it without a word.