Work with alternate keys
All Microsoft Dataverse records have unique identifiers defined as GUIDs. These are the primary key for each table. When you need to integrate with an external data store, you might be able to add a column to the external database tables to contain a reference to the unique identifier in Dataverse. This allows you to have a local reference to link to the Dataverse record. However, sometimes you can't modify the external database. With alternate keys you can now define a column in a Dataverse table to correspond to a unique identifier (or unique combination of columns) used by the external data store. This alternate key can be used to uniquely identify a record in Dataverse in place of the primary key. You must be able to define which columns represent a unique identity for your records. Once you identify the columns that are unique to the table, you can declare them as alternate keys through the customization user interface (UI) or in the code. This topic provides information about defining alternate keys in the data model.
Unsure about entity vs. table? See Developers: Understand terminology in Microsoft Dataverse.
Create alternate keys
You can create alternate keys programmatically or by using the customizations tools. For more information about using the customization tools, see Define alternate keys using Power Apps.
To define alternate keys programmatically, you first have to create an object of type EntityKeyMetadata (or use EntityKeyMetadata EntityType if working with Web API). This class contains the key columns. Once the key columns are set, you can use
CreateEntityKey to create the keys for a table. This message takes the table name and
EntityKeyMetadata values as input to create the key.
You should be aware of the following constraints when creating alternate keys:
Valid columns in key table definitions
Only columns of the following types can be included in alternate key table definitions:
Column Type Display Name DecimalAttributeMetadata Decimal Number IntegerAttributeMetadata Whole Number StringAttributeMetadata Single line of text DateTimeAttributeMetadata Date Time LookupAttributeMetadata Lookup PicklistAttributeMetadata Option Set
Valid key size
When a key is created, the system validates that the key can be supported by the platform, including that the total key size does not 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 will be displayed.
Maximum number of alternate key table definitions for a table
There can be a maximum of ten alternate key table definitions for a table in a Dataverse instance.
Unicode characters in key value
If the data within a column that is used in an alternate key will contain one of the following characters
?then retrieve (
GET), update or upsert (
PATCH) actions will not work. If you only need uniqueness then this approach will work, but if you need to use these keys as part of data integration then it is best to create the key on columns that won't have data with those characters.
Retrieve and delete alternate keys
If you need to retrieve or delete alternate keys, you can use the customization UI to do this, without writing any code. However, the SDK provides the following two messages to programmatically retrieve and delete alternate keys.
|Message request class||Description|
|RetrieveEntityKeyRequest||Retrieves the specified alternate key.|
|DeleteEntityKeyRequest||Deletes the specified alternate key.|
Monitor index creation for alternate keys
Alternate keys use database indexes to enforce uniqueness and optimize lookup performance. If there are lots of existing records in a table, index creation can be a lengthy process. You can increase the responsiveness of the customization UI and solution import by doing the index creation as a background process. The
EntityKeyMetadata.AsyncJob property (EntityKeyMetadata EntityType or EntityKeyMetadata) refers to the asynchronous job that is doing the index creation. The
EntityKeyMetadata.EntityKeyIndexStatus property specifies the status of the key as its index creation job progresses. The status could be any of the following:
- In Progress
When an alternate key is created using the API, if the index creation fails, you can drill into details about the cause of the failure, correct the problems, and reactivate the key request using the
ReactivateEntityKey (ReactivateEntityKey Action or ReactivateEntityKeyRequest message).
If the alternate key is deleted while an index creation job is still pending or in progress, the job is cancelled and the index is deleted.