Working with Alternate Keys in Microsoft Dataverse

In data modeling, ensuring data integrity and enabling efficient data integration are critical components of building reliable applications. Microsoft Dataverse provides a powerful feature called Alternate Keys to help achieve these goals. Alternate keys offer a way to uniquely identify records in a table using one or more fields other than the system-generated primary key (GUID). This feature is especially useful in integrations, data imports, lookups, and deduplication scenarios.

In this article, we’ll explore what alternate keys are, how they work, and how to use them effectively in your Power Platform or Dynamics 365 solutions.

What Are Alternate Keys?

By default, every table (entity) in Dataverse has a system-generated primary key—an automatically created GUID (Globally Unique Identifier). While this is effective for internal referencing, it’s not always ideal for external systems or human-readable data.

Alternate keys are one or more fields (attributes) in a table that can serve as an alternative, human-readable unique identifier for a record.

Example:

In a Product table:

System Primary Key: productid (GUID)
Alternate Key: productcode (e.g., “SKU1234”)

With alternate keys in place, you can reference records using logical keys instead of relying on the internal GUIDs.

✅ Benefits of Alternate Keys

1. Simplified Integration

External systems often refer to records by meaningful identifiers (e.g., email addresses, SKUs, usernames). Alternate keys allow you to reference these records directly in API calls or dataflows.

2. Improved Data Import

When importing data from Excel, CSV, or other databases, alternate keys allow Dataverse to match records without needing the GUID.

3. Upserts

Alternate keys enable upsert operations—that is, insert if the record doesn’t exist, or update if it does—without knowing the internal ID.

4. Enforce Uniqueness

Alternate keys enforce uniqueness constraints at the database level, helping avoid duplicate data.

How to Create Alternate Keys

Creating alternate keys is simple in the Power Apps Maker Portal:

Step-by-Step:

Go to Power Apps → Tables.
Select or create the table you want to configure.
Click on the “Keys” tab.
Click “+ New key”.
Select one or more fields that will form the alternate key.
Save and publish the table.

Only fields with certain data types can be used in alternate keys (more on that below).

Supported Field Types for Alternate Keys

Not all data types are supported for use as alternate keys. Supported types include:

Single line of text
Whole number
Decimal number
Date and time
Lookup (only for some scenarios, not recommended for multi-entity joins)

Unsupported types:

Multi-line text
Option sets
Two options (Boolean)
Image fields
Files and blobs

Always ensure the fields chosen are stable, predictable, and truly unique in your business context.

Example Scenario: Customer Integration

Suppose you’re integrating with a system that tracks customers by email address.

In Dataverse, create an alternate key on the Email field in the Contact table.
When a record is sent via the API or Power Automate, you can identify the contact using emailaddress1 instead of contactid.

Sample Upsert with Web API:

PATCH [Organization URI]/api/data/v9.2/contacts(email='jane.doe@example.com')
Content-Type: application/json

{
  "firstname": "Jane",
  "lastname": "Doe",
  "mobilephone": "555-1234"
}

If Jane exists (based on her email), her record will be updated. If not, a new record will be created.

Best Practices for Using Alternate Keys

✅ 1. Ensure True Uniqueness

Only use fields that are guaranteed to be unique in your data. For example, don’t use names, which can be duplicated.

✅ 2. Avoid Changing Key Values

Once set, avoid changing alternate key values frequently. Updates can cause issues with indexing and referential integrity.

✅ 3. Use for External Integrations

Alternate keys shine when syncing with external systems. They make record identification easier and more reliable.

✅ 4. Monitor Index Creation

When you create an alternate key, a SQL index is created in the background. In large environments, this can take time and resources. Avoid creating keys during peak times.

✅ 5. Use Minimal Fields

Use the fewest fields necessary to ensure uniqueness. Compound keys (keys with multiple fields) are harder to maintain and slower to query.

Limitations and Considerations

Key Creation is Asynchronous

When you create an alternate key, Dataverse queues an operation to build a SQL index. This can take several minutes, especially for large tables.

🕒 During this time, you can’t use the key in upserts or lookups.

Keys Must Not Contain Nulls

Dataverse enforces alternate keys only on rows where all key fields are populated. If a key field is null, the uniqueness rule doesn’t apply.

Cannot Use Certain Field Types

As mentioned, option sets, multi-selects, and other complex fields are not supported in alternate keys.

No Partial Matching

Alternate key matching is exact, not fuzzy or partial. Be sure data formatting is consistent.

Using Alternate Keys in Power Automate

When using Power Automate, alternate keys make it easier to update or reference existing records without needing to fetch the GUID first.

Example: Update a Contact by Email

Use the Update a row Dataverse action.
In the row ID field, use: contacts(email='john.doe@example.com')

This automatically locates the record using the alternate key (email).

Real-World Use Case: ERP Integration

Imagine you’re integrating a finance system (ERP) with Dataverse. The ERP uses Customer IDs to track client records.

In Dataverse, you create an alternate key on customerid in the Account table.
This lets your integration layer (API, Power Automate, Logic Apps) reference or update records based on ERP IDs.

Now you can run reliable upserts and ensure the ERP and CRM systems stay in sync—even if GUIDs differ between systems.

Alternate Keys vs Primary Name Fields

It’s important not to confuse alternate keys with primary name fields. Every table has a primary name field (e.g., fullname on Contacts), which is the friendly label for the record in the UI. However:

Primary name fields do not enforce uniqueness.
Alternate keys do enforce uniqueness and are used for lookup/upsert operations.

Alternate Keys and Dataflows

When using Power Query Dataflows to import data into Dataverse:

You can set alternate keys as the matching criteria.
This allows Dataflows to match and update existing records without needing GUIDs.