Create an alternate key in Dataverse

It is common to need a way to uniquely identify a record in a table. By default, all Microsoft Dataverse tables have a GUID as their only unique column. This GUID is called the Primary Key and it consists of a long string of numbers and letters that are not useful to a regular user regarding meaning or significance.

Alternate keys improve search and filtering and make it easier to work with data.

Defining a new key for a table allows us to identify a record in a more meaningful way by using a column that is familiar to users. When we define a column as a key, dataverse makes sure that every entry in that key column is required and unique so we can use the key column to distinctively identify a specific record. This can be especially helpful if we are integrating your data with an external system that uses that ID or number to identify a record (and not the Dataverse GUID). It also improves the search and filtering on the particular column because alternate key fields are always indexed.

Finally, keys can be based on a single column (Order ID) or a combination of fields, such as Financial year and Order ID. If you set a key on a column and try to enter duplicate data, the record will fail to save.

We can easily define an alternate key for a table by using the Power Apps portal with the following steps:

Step 1: Sign in to Power Apps with the organizational account, and then select Tables in the left-hand navigation.
Step 2: Select the table that we want to add a new key to.
Step 3: Select Keys from the options under Schema section.

Step 4: Select + New key in the command bar.

Step 5: A key dialog box appears at the right.
a) Give the key a display name.
b) We can also give the key’s internal name.
c) Select one or more columns that will make up the new key.
d) Select the Save button.

Step 6: After selecting Save, we will see the new key added to the list of all keys that are created for this table. We can create up to ten keys for each table. All values in the key will be unique.

It may take a few minutes for Dataverse to create the new key and indexes (depending on the size of our existing table). Then, we can start using it in our business solution.

Note: We cannot edit the key once its created, we can only delete that.

Click on Delete to confirm the deletion.

Tip If we have duplicated data in a column that is used by the key in multiple records, then the key will not be created. We can only create a key if the data in the column's existing record is unique across all records in the table.

Note: A key can be made up of one or many columns. If we select multiple columns, then the key is called a compound key. It would be helpful to validate entries if multiple fields together act as primary key (Composite primary key).