2012 Table Relationships

Table relations have undergone a drastic makeover in 2012. Let's take a peek.

Surrogate keys, Natural keys and Foreign key relations are the new concepts in 2012 tables. EDTs no longer support relations defined on them and Microsoft wants developers to avoid using Field fixed and Related field fixed relations and instead use Foreign key relations.

What's a Surrogate Key?

Surrogate key is

• A single column index on a table that uniquely identifies each record
• Also referred to as the Primary key, RecId index or the PrimaryIndex
• No business meaning
• System generated in Microsoft Dynamics AX 2012

Note: On new tables, the PrimaryIndex property will be set to SurrogateKey by default. Existing tables will NOT automatically have their PrimaryIndex property set to SurrogateKey.

And Natural Key?

• Defines a unique index that can be used instead of the SurrogateKey on lookup forms
• Optional Property
• User-friendly index with business meaning
• Also known as Alternate Key

Index properties
On table indexes, there is a new AlternateKey property. When set to Yes, this property allows for an index to be specified in the PrimaryIndex and NaturalKey properties on a table. The AlternateKey property can only be set to Yes on indexes with the AllowDuplicates property set to No since both the PrimaryIndex and NaturalKey properties require indexes that are unique.

Additionally on table indexes, a property called IncludedColumn has been added. A field with the IncludedColumn property set to Yes is added to a non-clustered index to improve the performance of a query by covering all of the fields that are referenced in a query using this index including the key and non-key fields. To set the IncludedColumn property to Yes, more than one field must exist on the index.

Relation properties

On a relation there is a property RelationshipType which can take 2 values. Composition and Association

In a Composition relationship, the parent table OWNS the child table. No records can be created in the child table without having a corresponding header in the parent table. For example, a sales line cannot exist without a sales header. A department can exist without an employee and an employee can be deleted without deleting the department, this relationship is an Association.

Foreign Keys: Parent/Child tables

Child table is one that has a foreign key column. Parent table is one that supplies the value for the foreign key column. Normal, Field fixed and Related field fixed table relations should be avoided going forward.

Try yourself.

Drag SalesId EDT on to your table fields. This will prompt you to add foreign key relation. SalesId field, SalesTable relation and SalesTableIdx index will be created for you.