Simple summary Keys are in Dynamics AX
There is a maximum of one
Primary Keyper table, whereas a table can have several
alternate keys. The
primary keyis usually the type of key that other tables, called child tables, refer to when a
foreign keyfield in those other tables need a relational identifier.
For new tables the default is a
primary keybased on the
RecIdfield , incremented number or a completely meaningless number that is generated by the system
As The RecId data type,
surrogate keysexist on a primary key table. As The RefRecId ETD, surrogate foreign keys exist on a foreign key table (Ex:
Partyfield is on
Alternate keycan be chosen as the
Replacement Keyof a table that can display on forms instead of a meaningless numeric
primary keyvalue. Each table can have a maximum of one
Natural keyhas meaning to people. A set of fields that uniquely identify a record and would have formed the primary key of the table, if not for the existence of a
Relations represents a
The use of surrogate keys has a history that is crucial to comprehend. These keys were initially introduced in AX 2012 to enhance performance and enable features such as the ledger account lookup when inputting general ledger journals. However, their hardcoded nature as RecId led to problems when foreign key relations were added, resulting in an unhelpful 64-bit integer being created in the field. To address this, an alternate key was introduced, which can be set as a property on the index definition to establish a more meaningful relationship for foreign keys. The primary key can only be set on unique indexes that have the Alternate Key property enabled. Another type of key introduced was the replacement key, which enables the display of a meaningful key instead of the numeric RecId-based SurrogateKey. Although SurrogateKey allows the use of RecId as a foreign key, it displays useful information from a field group on the parent table. For example, ReferenceGroup control showing fields from a field group on the related table.
There are several drawbacks to using surrogate keys, including the inability to use tables without a natural index as a primary key in a data entity, difficulty using tables with the Open in Excel experience, and increased complexity in data transfer, reporting, and business intelligence. Please use natural key for these scenarios.
Thank you for reading!