Work with alternate keys
Effective November 2020:
- Common Data Service has been renamed to Microsoft Dataverse. Learn more
- Some terminology in Microsoft Dataverse has been updated. For example, entity is now table and field is now column. Learn more
This article will be updated soon to reflect the latest terminology.
All Microsoft Dataverse records have unique identifiers defined as GUIDs. These are the primary key for each entity. 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 an attribute in a Dataverse entity 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 attributes represent a unique identity for your records. Once you identify the attributes that are unique to the entity, 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.
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 attributes. Once the key attributes are set, you can use
CreateEntityKey to create the keys for an entity. This message takes the entity name and
EntityKeyMetadata values as input to create the key.
You should be aware of the following constraints when creating alternate keys:
Valid attributes in key definitions
Only attributes of the following types can be included in alternate key definitions:
Attribute 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 definitions for an entity
There can be a maximum of 5 alternate key definitions for an entity in a Dataverse instance.
Unicode characters in key value
If the data within a field 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 fields 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.