Dataverse: Duplicate Detection Rules vs Alternate Keys

Introduction

Having clean data in Dataverse can be tricky. One of the challenges is detecting and managing duplicate records.
With out-of-the-box tools you can implement duplicate detection rules or create alternate keys.
If you are a developer, you can implement a custom validation in a plugin.
In this article we will focus on out-of-the-box features (duplicate detection rules and alternate keys) and compare them.

Duplicate detection rules

First step is to check if the feature Duplicate Detection is enabled in your environment (it is enabled by default):

1. Connect to Dynamics 365 and open Power Platform Environment Settings app
2. Click on Data Management on the left pane and select Duplicate detection settings
   
    
3. Check that the option Enable duplicate detection is enabled
   
   
   There are 3 triggers that you can activate:

• When a record is created or updated
• When Microsoft Dynamics 365 for Outlook goes from offline to online
• During data import

Second step is to make sure the feature is enabled for your table:

1. Open Power Apps Portal and select your environment
2. Select your table on the left pane and click on Properties
   
    
3. Expand Advanced options pane and ensure that Apply duplicate detection rules is checked
   

Then you can create duplicate detection rules:

1. Connect to Dynamics 365 and open Power Platform Environment Settings app
2. Click on Data Management on the left pane and select Duplicate detection rules
   
    
3. All out-of-the-box duplicate detection rules are displayed
   
    
4. Click on New on the command bar
   Below an example with the out-of-the-box rule Contacts with the same first name and last name for Contact table
   
   
   Provide a name and description for the rule and define the criteria for identifying duplicates:

• Base Record Type: select the type of record the rule applies to (e.g., Contacts).
• Matching Record Type: select the type of record to compare. Typically, this is the same as the base record type, but you can compare different types (e.g., Contacts and Leads).
• Exclude Inactive Records: check this box if you want to exclude inactive records from the duplicate detection process.
• Case-Sensitive: check this box if the rule should be case-sensitive.
• Field Selection: for each criterion, select the fields to compare and the operator (e.g., Exact Match).
• Ignore Blank Values: check this box if you do not want the rule to consider blank fields as equal.
  
  You can add several criterions to your rule criteria:
  For text fields you have 3 choices:
  - Exact Match
  - Same First Characters
  - Same Last Characters
  For Datetime fields you have 2 choices:
  - Same date
  - Same date and time
  For whole number/decimal and lookup fields only Exact Match is available

      5. After configuring the criteria, click on Save.
          To make the rule active, select it and click on Publish.
          Note that you can only publish up to five rules for the same base record type at a time.

Now let's try creating a duplicate record using Dynamics 365 UI. For example a contact with existing first name and last name.
You should see a pop-up dialog with the warning message Duplicate records found.

You have 3 possibilities:

• Ignore and save : if you are certain the record is unique or requires a new entry despite the match, click "Ignore and Save" to create the duplicate
• Merge : if you want to combine the information from both records into a single, primary record, select "Merge." You will typically be prompted to choose which record will be the primary one, and then you can select which fields to keep from each record
• Cancel : do not create the duplicate record

Alternate keys

An alternative to duplicate detection rule is to create alternate keys.
This method is the same as creating unique-index(es) from the database perspective. Hence this method will be more strict compared to duplicate detection rules.
An alternate key can combine one or several columns.
It can contain multiple rows with NULL values. NULL values are not indexed.

Some limitations:

• There can be a maximum of 10 alternate keys for a table in a Dataverse instance
• Columns used in the key must not have field-level security applied
• Alternate keys aren't supported in virtual tables
• 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. If the key size doesn't meet the constraints, an error message is displayed
• 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

To create an alternate key for a specific table:

1. Open Power Apps Portal and select your environment
2. Select the table into which you want to create an alternate key
3. Click on Keys
4. Provide a display name and select the columns to include in your key
   In the example below I create an alternate key in table Contact for columns birthday and last name

   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

5. Click on Save. The system will automatically create the index in the database.
   If the system detects duplicate data in the table, the creation will fail and you need to delete data first.
   If there are lots of existing records in your table, index creation can be a lengthy process.
   During creation of the key status will be Pending. Once the creation is complete status changes to Active.
   

Now let's try creating a duplicate record using Dynamics 365 UI. To be consistent with the alternate key created previously, a contact with existing birthday and last name.
You should see a pop-up error with the message Duplicate record.

Therefore, it is impossible to create a duplicate record with the user interface. This makes sense as uniqueness is ensured at the database level.

Creating/Updating records programmatically

We have seen that behavior is different when we create a duplicate record with the UI using duplicate detection rules or alternate keys.
But what happens if we create duplicate records programmatically with Dynamics 365 SDK or Web API?

Duplicate detection rules

By default duplicate detection rules are not applied when you are creating or updating records using Web API or Dynamics 365 API. Therefore duplicate records will be created.
If you want Dynamics 365 to raise an error you must utilize an optional parameter.
With Web API use parameter MSCRM.SuppressDuplicateDetection in the header of your HTTP request (POST to create a record or PATCH to update a record)  to avoid creation of a duplicate record of an existing record.
The value assigned to MSCRM.SuppressDuplicateDetection determines whether the Create or Update operation can be completed:

• true: Create or update the record, if a duplicate is found.
• false:  Do not create or update the record, if a duplicate is found.

So ensure that MSCRM.SuppressDuplicateDetection is set to false in your Web API request to detect duplicates.
You will get the following error message if you try to create a duplicate record:

{
  "error": {
    "code": "0x80040333",
    "message": "A record was not created or updated because a duplicate of the current record already exists."
  }
}

Note that if you do not use MSCRM.SuppressDuplicateDetection duplicates will be created.
Below an example of POST request to create a contact named "John Doe" with activation of duplicate detection rules.

POST [Organization URI]/org1/api/data/v9.2/contacts
If-None-Match: null
OData-Version: 4.0
OData-MaxVersion: 4.0
Content-Type: application/json
Accept: application/json
MSCRM.SuppressDuplicateDetection: false

{
    "firstname":"John",
    "lastname":"Doe"
}

And below an example performed with XrmToolbox plugin WebAPI Launcher.
Note parameter MSCRM.SuppressDuplicateDetection in the header.

And the result of this Web API request if we try to create a duplicate:

With Dynamics 365 SDK you have to use SuppressDuplicateDetection optional parameter in your request (CreateRequest or UpdateRequest) if you want the configured duplicate detection rules to run and throw an exception while creating or updating a record.
So set its value to false in your code to trigger duplicate detection rules.
You will get the following error if you try to create a duplicate record:
A record was not created or updated because a duplicate of the current record already exists

Below a C# example to create with the SDK a contact named "John Doe" with activation of duplicate detection rules.

namespace CrmDuplicateDetection
{
    class Program
    {
        static void Main(string[] args)
        {
            CrmServiceClient.MaxConnectionTimeout = TimeSpan.FromHours(3);
            var connectionString = WebConfigurationManager.AppSettings["connectionString"];
            var client = new CrmServiceClient(connectionString);
 
            var contact = new Entity("contact")
            {
                ["firstname"] = "John",
                ["lastname"] = "Doe"
            };
 
            var createRequest = new CreateRequest { Target = contact };
            // Pass parameter 'SuppressDuplicateDetection' to trigger Duplicate Detection Rules
            createRequest["SuppressDuplicateDetection"] = false;
            var response = (CreateResponse)client.Execute(createRequest);
        }
    }
}

Alternate keys

If you try to create a duplicate record programmatically with Dynamics 365 SDK you will get an error even if you set parameter SuppressDuplicateDetection to false in your CreateRequest/UpdateRequest request (and same if you set MSCRM.SuppressDuplicateDetection to false in the header of your Web API request).

Below an example of Web API POST request to create a contact with last name and birthdate that already exist in Dataverse.

Conclusion

As we have seen alternate keys are a strict way to avoid duplicates creation and ensure data correctness.
It is not possible to create duplicate records using Dynamics 365 user interface or Web API/Dynamics 365 SDK.
But alternate keys have some limitations (10 alternate keys max per table, not supported in virtual tables, cannot include columns with field level security enabled, no operator Same First/Last characters in the criteria).
Duplicate detection rules are more flexible when creating duplicate records through the UI (ability to merge records or force the creation of duplicates).
And with Web API/Dynamics 365 SDK you can choose whether you want to apply duplicate detection rules when creating/updating records.
So the choice between duplicate detection rules and alternate keys will depend on whether you want to allow duplicates creation under certain circumstances.
If you never want to create duplicate records (either through the user interface or programmatically) alternate keys are a good solution because the check is done at database level.
But if you want to warn users when they try to create a duplicate record in the UI and allow them to confirm or not the duplicate creation (and use Merge feature) duplicate detection rules are a good choice.
The same is true if you have interfaces between a third-party system and Dynamics 365 (an Azure webjob or Azure function for instance) and want to allow or not creation of duplicates.
But if you have an existing interface that allows the creation of duplicate records and want to change this behavior, you will have to modify the source code of the interface to use SuppressDuplicateDetection parameter to trigger duplicate detection rules.