When creating a Dynamics 365 Finance and Operations data entity, developers often encounter the frustrating “Natural key not found” error. This typically happens when the source table does not contain a unique index required for a data entity primary key.
In this guide, we’ll explain why Dynamics 365 requires a natural key, and walk through three practical approaches for creating a data entity for a table without a natural key.
Error:
“The natural key for the table was not found.”
Cause:
The source table does not contain a unique index required for a Dynamics 365 data entity primary key.
Fix Options:
This article is intended for:
A Dynamics 365 data entity is a de-normalized SQL view used for data import, export, and integrations through the Data Management Framework.
Data entities make it possible to move data in and out of the system without directly interacting with underlying tables. Because of this, each data entity must have a primary key that uniquely identifies records.
When using the data entity wizard, you may see the error:
“Natural key for the table was not found.”
Dynamics 365 requires every data entity to have a primary key so incoming data can be matched to existing records during import or integration.
If the source table lacks a unique index, the wizard cannot determine how records should be identified, which results in this error.
This situation is common when working with custom tables or legacy designs that were never intended for integration scenarios.
For this example, a simple table, TestTable, has been created. The table contains three fields and does not have a unique index for now.
Generating a new data entity for it ends up with the error shown below.
The error comes from the system requirement that every data entity must have a primary key defined. So, it is possible to uniquely identify each row of the data that is conveyed through a data entity. For instance, Data Management Framework always evaluates data rows coming in for uniqueness using the data entity primary key. If a data row already exists in the system, then it gets selected for update, otherwise the data is inserted.
Most often, changing an already existing index on a table to being unique cannot be considered as a solution to the problem. This post is aimed at outlining several workarounds of how the issue can be overcome with "little blood".
Below are three proven approaches to creating a Dynamics 365 data entity without a natural key.
The first approach is based on adding a new LineNum field (incremental identifier of Real type) to a table and building up a new table unique index based on it.
This makes a difference and allows generating the data entity by standard means of Visual Studio for Dynamics 365, for instance by Data entity wizard.
Table TestTable should also be supplied with a piece of code for the LineNum field default initialization, similar to that in the screenshot below.
Additionally, if there is any data in the original table, SQL update script needs to be prepared and executed in order to correctly initialize the missing LineNum values and eliminate DB synchronization errors.
The second approach is premised on the assumption that a predefined combination of input columns (or one column) can be used to uniquely identify each row of the input data consumed by a data entity. Data entity is much of a View object and its primary key may include different set of fields than that found in the unique index of its primary data source table. In other words, we can build up a data entity with a primary key that contains almost any valid field from its data source(s) and get it working properly.
However, there are two important prerequisites:
1) Combination of values in columns that correspond to the data entity primary key fields must be unique for each row of the incoming data.
2) Incoming data must not violate data integrity enforced by the updated data sources that a data entity is built on.
In our case, we’ll start with an assumption that the Name field (or combination of other fields) can be taken as a data entity primary key, even though it is part of the non-unique table index. Provided that incoming names are unique for each data row, we are not expected to have troubles during import.
Standard "The natural key not found" validation check can be bypassed by using the following simple workarounds:
Proceed with the wizard and then adjust appropriately the newly created data entity and staging table.
The third approach is based on the fact that a data entity primary key can be built on the primary data source RecId field. Standard data entity General Journal Account Entry Entity is a vivid example of applying such a technique.
Although that conceptually contradicts the main data entity paradigm, the workaround may be useful when there is a need to prepare a data entity for data import/export without troubling to much about changing the primary data source table or manipulating with a set of fields for the data entity primary key.
Each option has tradeoffs:
| Approach | Best For | Considerations |
|---|---|---|
| Add LineNum | Long-term architecture | Requires table change |
| Composite Key | Existing unique fields | Can be complex |
| RecId Key | Quick workaround | Less ideal conceptually |
The right choice depends on your Dynamics 365 Finance & Operations development goals and integration needs.
If you're working through data entities, integrations, or migrations, our team regularly supports organizations with:
Learn more about our Dynamics 365 services and support.
What is a natural key in Dynamics 365?
A natural key uniquely identifies each record in a data entity so the Data Management Framework can determine whether to insert or update records.
Can a Dynamics 365 data entity use RecId as a primary key?
Yes. RecId can be used as a practical workaround when a table lacks a unique index.
Why does a data entity require a primary key?
The Data Management Framework uses the primary key to determine whether incoming records already exist or should be inserted.
What happens if a table has no unique index?
You must create a workaround such as adding a unique field, building a composite key, or using RecId.
What causes the “Natural key not found” error?
This error occurs when the data entity wizard cannot find a unique index to use as the primary key.