Events
Power BI DataViz World Championships
Feb 14, 4 PM - Mar 31, 4 PM
With 4 chances to enter, you could win a conference package and make it to the LIVE Grand Finale in Las Vegas
Learn moreThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Alternate keys provide an efficient and accurate way of integrating data with external systems. It’s essential in cases when an external system doesn’t store the Globally Unique Identifier (GUID) IDs that uniquely identify rows in Microsoft Dataverse.
A data integration system uses alternate keys to uniquely identify rows using one or more table column values that represent a unique combination. Each alternate key has a unique name.
For example, to identify an account row with an alternate key, you can use the account number or the account number column in combination with some other columns, which have values that shouldn't change.
Note
While you can define alternate keys with Power Apps, they can only be used programmatically in code. To learn more about using alternate keys programmatically, see:
Some of the benefits of the alternate keys feature include:
There are two designers you can use to create alternate keys:
Designer | Description |
---|---|
Power Apps portal | Provides an easy streamlined experience, but some options aren't available. More information: Define alternate keys using Power Apps portal |
Solution explorer | Not as easy, but provides for more flexibility for less common requirements. More information: Define alternate keys using solution explorer |
Note
You can also create an alternate key in your environment using the following:
Information in this article helps you choose which designer you can use.
You should use the Power Apps portal to create alternate keys unless you need to address any of the following requirements:
There are constraints on alternate key creation.
Only these kinds of columns can be used to create alternate keys:
Note
You can define up to 10 different keys for a table.
When a key is created, the system validates that the key can be supported by the platform, including that the total key size doesn't violate SQL-based index constraints like 900 bytes per key and 16 columns per key. If the key size doesn’t meet the constraints, an error message is displayed.
If the data within a column that is used in an alternate key contains one of the following characters <
,>
,*
,%
,&
,:
,/
,\\
,#
then update or upsert (PATCH) actions won't work.
If you only need uniqueness, then this approach works, but if you need to use these keys as part of data integration then it's best to create the key on columns that doesn't have data with these characters.
When an alternate key is created, it initiates a system job to create indexes on the database tables to enforce unique constraints on the columns used by the alternate key. The alternate key won't be in effect until these indexes are created. Creating these indexes might take some time depending on the amount of data in the system.
The status of the system job determines the state of the alternate key. The alternate key can have the following states:
When the system job is completed, the alternate key status is Active and it's available for use.
If the system job fails, locate the system job to view any errors. The system job will have a name that follows this pattern: Create index for {0} for table {1}
where 0
is the Display Name of the alternate key and 1
is the name of the table.
Note
If you want to monitor the status of the system job you should use solution explorer to create the index. It will include a link to the system job so you can monitor it. More information: (Optional) View the system job tracking creation of indexes
Define alternate keys using Power Apps portal
Define alternate keys using solution explorer
Developer Documentation: Define alternate keys for a table
Developer Documentation: Use an alternate key to create a row
Events
Power BI DataViz World Championships
Feb 14, 4 PM - Mar 31, 4 PM
With 4 chances to enter, you could win a conference package and make it to the LIVE Grand Finale in Las Vegas
Learn moreTraining
Learning path
Use advance techniques in canvas apps to perform custom updates and optimization - Training
Use advance techniques in canvas apps to perform custom updates and optimization
Certification
Microsoft Certified: Power Platform Developer Associate - Certifications
Demonstrate how to simplify, automate, and transform business tasks and processes using Microsoft Power Platform Developer.
Documentation
Work with alternate keys (Microsoft Dataverse) - Power Apps
The topic explains about how to create alternate keys for a table. Alternate keys can be created programmatically or by using the customization tools
Define alternate keys using Power Apps - Power Apps
Learn how to define alternate keys using Power Apps (make.powerapps.com)
Use an alternate key to reference a record - Power Apps
Alternate keys can be used to create instances of Entity and EntityReference classes. This article discusses the usage patterns and possible exceptions that might be thrown when using alternate keys.