Edit

Work with alternate keys

  • Article

All Microsoft Dataverse table rows 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 row. 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 row in Dataverse in place of the primary key. You must be able to define which columns represent a unique identity for your rows. 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.

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.

  • Not supported in virtual tables

    Alternate keys are not supported in virtual tables because we can't enforce uniqueness when the data is on another system. More information: Get started with virtual tables (entities)

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.

To retrieve all the keys for a table, use the Keys property of EntityMetadata (EntityMetadata EntityType or EntityMetadata class). It gets an array of keys for a table.

Use this Web API query to view all tables and see which ones have alternate keys:

GET [Organization URI]/api/data/v9.2/EntityDefinitions?$select=SchemaName&$expand=Keys($select=KeyAttributes)

Some examples returned by this request:

{
    "SchemaName": "Account",
    "MetadataId": "70816501-edb9-4740-a16c-6a5efbc05d84",
    "Keys": [
        {
            "KeyAttributes": [
                "accountnumber"
            ],
            "MetadataId": "1dc7b1d2-6beb-ec11-bb3d-0022482ea769"
        }
    ]
},
{
    "SchemaName": "example_Table",
    "MetadataId": "8f521e41-8934-ec11-b6e6-002248242f3b",
    "Keys": [
        {
            "KeyAttributes": [
                "example_key1",
                "example_key2"
            ],
            "MetadataId": "2f16d0c6-88ea-ec11-bb3d-0022482ea769"
        }
    ]
}

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:

  • Pending
  • In Progress
  • Active
  • Failed

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.

See also

Use an alternate key to reference a record
Use change tracking to synchronize data with external systems
Use Upsert to insert or update a record
Define alternate keys to reference records