What is Primary Index, Cluster Index, Replacement Key, and Alternate Key?
Author: Nghia Song - Microsoft Dynamics 365 Technical Consultant
Date: September 25, 2022
Category: Dynamics 365 FnO, Dynamics 365 FO
Primary Index
The Primary Index uniquely identifies records in any given table. Each unique combination of Primary Key fields ensures that only one record matches. There can only be one primary index per table.
Example: In the CustTable, the field AccountNum is often set as the primary index. Each customer has a unique AccountNum.
Cluster Index
The Cluster Index controls the physical storage order of the data in the table. It does not need to be unique.
Example: In a sales order table, if you frequently search or sort by OrderDate, it can be beneficial to use OrderDate as the cluster index.
Replacement Key
A Replacement Key defines what field is shown in lookup forms instead of the RecId. It’s helpful when RecId is used as the surrogate primary key, but you want something meaningful shown to users.
Example: In a form where you're selecting a vendor, instead of showing RecId like "5637144576", the system shows the VendorAccount field as a replacement key like "VEND-001".
Alternate Key
A Alternate Key is a unique key used as an alternative to the primary key, often to establish relations with other tables. You can have multiple alternate keys.
Setup Requirements:
- Create a new index
- Set
AllowDuplicates = No - Set
AlternateKey = Yes
AllowDuplicates is not set to "No", then AlternateKey will be disabled (greyed out).
Example: In InventTable, the ItemId field is used as an alternate key to ensure item uniqueness and for lookups/relations to other tables like InventTrans.
Contact Information
Tel / WhatsApp: +84 967 324 794 / +356 7748 2386
Email: nghia@songnghia.com
Author Profile: Song Nghia - songnghia.com
No comments:
Post a Comment