Table Keys: Surrogate, Alternate, Replacement, Primary, and Foreign

Primary key

A primary key is one type of key. The other type of key is an alternate key. 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.

Starting in Microsoft Dynamics AX 2012 the primary key for every new table is always enforced by an index that has exactly one field. The one field is usually an incremented number or a completely meaningless number that is generated by the system. For new tables the default is a primary key based on the RecId field. This is represented as the surrogate key in the user interface.

The following table describes the PrimaryIndex property and other major properties that are related to keys.

Property	Description
PrimaryIndex	The drop-down list contains the surrogate key plus every index on the table that has its AlternateKey property set to Yes.
CreateRecIdIndex	This property controls whether the system creates a unique index on the RecId field. The default value is Yes. This is the basis of the surrogate key. No other field is added to this index, not even DataAreaId.
ReplacementKey	The drop-down list contains every index that has its AlternateKey property set to Yes. You might change the default blank value to an index whose field values within each record provide a name or other moniker that is meaningful to people. If a ReplacementKey is chosen, its fields can appear on forms to helpfully identify each record. The ReplacementKey should be a set of fields that represent the natural key.
ClusterIndex	The ClusterIndex value is given to the underlying Microsoft SQL Server database system as a performance tuning choice. This choice generally controls the physical sequence in which the records are stored in the underlying database.

Alternate Key

A table can have several alternate keys. Any one alternate key can switch to being the primary key, if the alternate key is comprised of only one field.

A table can reference the alternate key of another table. However, it is more common for a table to reference the primary key of another table. As an option, an alternate key can be chosen as the ReplacementKey of a table.

In practice each alternate key relies on a unique index for its implementation and enforcement. However, a unique index alone does not make an alternate key. The AlternateKey property must be set to Yes to make a unique index be an alternate key.

The following table describes properties on the AOT node for an index.

ALTERNATE KEY

Property	Description
AllowDuplicates	No means that the combined fields of the index must together make a value in each record which no other record has.
AlternateKey	Yes means that other tables can create foreign key relations that reference this key, as an alternative to referencing the primary key. Indexes with two or more fields cannot have their AlternateKey property value set to Yes.
ValidTimeStateKey	A key that is marked as a valid time state key is not a candidate key for child tables to reference in their foreign key relations. Instead, this key is meant for managing date effective data in its own table. The default is No. This field can be Yes only if the ValidTimeStateFieldType property is Yes on the table. Yes means this key contains the ValidFrom and ValidTo fields. The ValidTimeStateKey property cannot be set to Yes when the AlternateKey property is set to No.

Surrogate Key

PRIMARY KEY

"A surrogate key is a unique identifier that is used to replace a natural key in a database. Surrogate keys are used in D365FO to improve performance and data integrity. For example, the RecId field is a surrogate key in D365FO."  "A natural key is a column or a set of columns that uniquely identifies a row in a table. A surrogate key is a column or a set of columns that uniquely identifies a row in a table, but it does not have any business meaning." "The main difference between natural keys and surrogate keys is natural keys are often used in data analysis and reporting, while surrogate keys are often used for performance and data integrity reasons." An example of a natural key is a customer ID. An example of a surrogate key is a RecId "Natural keys have the benefit of having business meaning, but they can be less efficient and less secure than surrogate keys. Surrogate keys have the benefit of being more efficient and more secure, but they can be less intuitive and less useful for data analysis and reporting." Foreign key  "A foreign key is a column or a set of columns in a table that refers to the primary key of another table. It is used to establish a relationship between two tables." "Foreign keys are used in D365FO to enforce referential integrity between tables. Referential integrity ensures that the data in one table is consistent with the data in another table." "When you create a foreign key, you are essentially saying that the value in the foreign key column must also exist in the primary key column of the other table. If you try to insert a row into a table with a foreign key that does not have a corresponding row in the other table, the database will reject the insert." example: "The CustomerId field in the SalesOrderHeader table is a foreign key that refers to the CustomerId field in the Customer table. This ensures that every sales order must have a corresponding customer."