The 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.
Some of the benefits of the alternate keys feature include:
- Enforce uniqueness
- Faster lookup of the rows (use database indexes to optimize lookup performance)
- More robust bulk data operations
- Simplified programming with data imported from external systems without row IDs
When creating alternate keys, it is important to consider the following constraints:
- Only columns of the following types can be included in alternate key table definitions:
- Decimal Number
- Whole Number
- Single line of text
- Date Time
- Lookup
- Option Set
- Attributes used in the key must not have field-level security applied
- There can be a maximum of ten (10) alternate key table definitions for a table in a Dataverse instance
- Valid key size
- When a key is created, the system validates the key, including that the total key size doesn't violate SQL-based index constraints like 900 bytes per key and 16 columns per key.
- Alternate keys aren't supported in virtual tables because we can't enforce uniqueness when the data is on another system
- Unicode characters in key value
- If the data within a column that is used in an alternate key contains one of the following characters /,<,>,*,%,&,:,\\,?,+ then retrieve (GET), 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 won't have data with those characters.
Another interesting technical information is that alternate key can contain multiple rows with NULL values. The NULL values are not indexed.
Julien Biedermann
Add new comment