web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Table Keys : Surrogate...
Microsoft Dynamics AX (Archived)

Table Keys : Surrogate , Replacement , Alternate , Primary and foreign key in AX 2012

(0) ShareShare
ReportReport
Posted on by @rp@n 30

Dear All,

I read msdn documents but not properly understand why "Surrogate , Alternate and Replacement key" is introduced in AX 2012.
Can you please explain me with proper real scenario of"Surrogate , Alternate and Replacement keys". How it's work practically in table level. What is the advantage of those keys?

Thanks!

Arpan Sen

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Mea_ Profile Picture
    Mea_ 60,284 on at
    RE: Table Keys : Surrogate , Replacement , Alternate , Primary and foreign key in AX 2012

    Hi @rp@n,

    Surrogate key has couple of advantages over natural key:

    You don't have "renaming issue", so if you are using city name as a key and you would like to rename it, it may be very complex because this value could be already used in multiply tables.

    It is easier to maintain and they consume less space because it is only one int64 field comparing to composition field, for example name + middle name + surname.

    The only disadvantage is that they are not readable for end users, however it is solved by  Replacement key in AX.  Replacement  key is unique natural key that would be shown to the user in AX UI instead of surrogate key.

  • @rp@n Profile Picture
    @rp@n 30 on at
    RE: Table Keys : Surrogate , Replacement , Alternate , Primary and foreign key in AX 2012

    Thanks Ievgen,

    Sorry, I not understand properly.

    Could you please give me an example 1 by 1 of Surrogate, Alternate, Replacement keys.

    Thanks!

    Arpan Sen

  • Mea_ Profile Picture
    Mea_ 60,284 on at
    RE: Table Keys : Surrogate , Replacement , Alternate , Primary and foreign key in AX 2012

    What exactly is not clear to you ? )

  • @rp@n Profile Picture
    @rp@n 30 on at
    RE: Table Keys : Surrogate , Replacement , Alternate , Primary and foreign key in AX 2012

    Hi Levgen,

    Could you please give an example 1 by 1 keys with advantage.

    Why these keys are introduced in AX 2012 ?

    1. Surrogate

    2. Alternate

    3. Replacement

    *** How it's work in practical scenario.

    Thanks!

    Arpan Sen

  • Mea_ Profile Picture
    Mea_ 60,284 on at
    RE: Table Keys : Surrogate , Replacement , Alternate , Primary and foreign key in AX 2012

    HI Orpan,

    Just read this www.agiledata.org/.../keys.html natural or surrogate key is more SQL thing than AX, in AX it was introduced because of same reasons.

    The only thing that does not exists in "normal" sql is a Replacement  key.

    It is unique natural key that would be shown to the user in AX UI instead of surrogate key, for e.g. you can create table MyCat.

    Primary key will be surrogate and replacement - "CatName". All relations in AX will be done to surrogate key, however on UI you will see "CatName" on related forms and all lookups will show "CatName".

    To do this magic new control was introduced in AX 2012 community.dynamics.com/.../replacement-groups-in-ax-2012

  • @rp@n Profile Picture
    @rp@n 30 on at
    RE: Table Keys : Surrogate , Replacement , Alternate , Primary and foreign key in AX 2012

    Thanks Ievgen,

    I will go through and let you know.

  • @rp@n Profile Picture
    @rp@n 30 on at
    RE: Table Keys : Surrogate , Replacement , Alternate , Primary and foreign key in AX 2012

    Thanks

  • Mea_ Profile Picture
    Mea_ 60,284 on at
    RE: Table Keys : Surrogate , Replacement , Alternate , Primary and foreign key in AX 2012

    If any of the answers helped you can you please mark it as verified ?

  • @rp@n Profile Picture
    @rp@n 30 on at
    RE: Table Keys : Surrogate , Replacement , Alternate , Primary and foreign key in AX 2012

    Hi Ievgen,

    Header table : EmpTable
    Fields : EmpID , Name
    Note : NameIdx index must be create 1ST because it’s having Alter Alternate Key = YES

    Fields

    Index

    Allow Duplicate

    Alternate Key

    Name

    NameIdx

    No

    Yes

    EmpID

    EmpIdIdx

    No

    No


    Note : Here Auto Identification is automatically populated because Name index we set to property Alternate Key = YES.

    Emptable : Properties

    In EmpTable properties : ReplacementKey = NameIdx

    Line Table : EmpTransaction
    Fields : WorkdHrs

    Relation : Then create Foreign key -- Primary key based relation with Header table (EmpTable)

    Note : It will automatically create relation with HeaderTable (EmpTable.RecID).

    Once relation is done it will add automatically field EmpTable and add index EmpTableIdx in to Line table (EmpTransaction).

    Create records :

    Header Table : EmpTable

    Line Table : EmpTransaction

    Note : In EmpTable fields we are getting values from HeaderTable (Emptable) with RecIDs in to EmpTransaction table.

    Form : EmployeeDetails 

    Add two data sources ( EmpTable, EmpTransaction)
    Design : Add to sections
    Header : EmpTable
    Footer : EmpTransaction

    Once I drag and drop EmpTable field from EmpTransaction table in to Footer GRID

    Then it will automatically created Reference Group for EmpTable field because of nameIdx set to Alternate key = YES

    Output :

    8880.as.png

    Note : In Line table (EmpTransaction) it’s store the RecID but in GUI it showing Value respective of that RecID as reference.

    This is the concept of Alternative key and Replacement key. Please let me know is't correct?

    Waiting for your response.

    Thanks!

    Arpan Sen

     

  • @rp@n Profile Picture
    @rp@n 30 on at
    RE: Table Keys : Surrogate , Replacement , Alternate , Primary and foreign key in AX 2012

    Dear all,

    Could you please any one verified the above scenario is correct?

    Thanks in advance!

    Regards,

    -Arpan Sen

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Community Member Profile Picture

Community Member 4

#2
Nayyar Siddiqi Profile Picture

Nayyar Siddiqi 2

#2
NNaumenko Profile Picture

NNaumenko 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans