Work with alternate keys

All Common Data Service 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 Common Data Service. This allows you to have a local reference to link to the Common Data Service record. However, sometimes you can’t modify the external database. With alternate keys you can now define an attribute in a Common Data Service 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 Common Data Service 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 to reference CRM records.

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 Common Data Service 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 get or 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.

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

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

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