Skip to main content

Simple summary Keys are in Dynamics AX

  • There is a maximum of one Primary Key per table, whereas a table can have several alternate keys. The primary key is usually the type of key that other tables, called child tables, refer to when a foreign key field in those other tables need a relational identifier.

  • For new tables the default is a primary key based on the RecId field , incremented number or a completely meaningless number that is generated by the system surrogate key.

  • As The RecId data type, surrogate keys exist on a primary key table. As The RefRecId ETD, surrogate foreign keys exist on a foreign key table (Ex: Party field is on CustTable Table).

  • Alternate key can be chosen as the Replacement Key of a table that can display on forms instead of a meaningless numeric primary key value. Each table can have a maximum of one replacement key.

  • Natural key has 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 surrogate key.

  • Relations represents a foreign key.

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!