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

Notifications

Announcements

Season of Giving Solutions is Here!
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Foreign Key Relation w...
Microsoft Dynamics AX (Archived)

Foreign Key Relation with Field Fixed Constraint

(1) ShareShare
ReportReport
Posted on by Pete Alberts 3,542

I was wondering why this is not possible. If I add a foreign key relation to a table, the field fixed constraint is not available.

I want to store a reference to two different tables depending on an enum value. For example:

1) if TableA.VehicleType == VehicleType::Sedan then TableA.VehicleRecId = SedanTable.RecId

2) if TableA.VehicleType == VehicleType::Truck then TableA.VehicleRecId == TruckTable.RecId

Do I maybe misunderstand the purpose of a field fixed constraint?

Further I have noticed the following in standard AX implementations:

1) The ParentTableId and ParentRecId method

2) Normal relation with field fixed constraint

Which do cover the solution, but a foreign key with a field fixed constraint would be ideal.

Thanks for reading

*This post is locked for comments

I have the same question (0)
  • Verified answer
    Koen Roks Profile Picture
    Koen Roks on at

    Hi Pete,

    AX only accept FK relations between the PK of the other table (In this case the PK of the Sedan or Truck table) which will recid in most cases. As you suggest you could work around this "Constraint" by using the normail relation.

    From a pure database perspective this would be interesting. But ax does not make "real" FK constraints on the database level. AX uses these relations internally to create lookups, do validations, .... 

    Kind Regards,

    Koen

  • Mea_ Profile Picture
    Mea_ 60,284 on at

    Hi Pete Alberts,

    Foreign key is supposed to be primary key on the table and how primary key on the TruckTable table could have field fixed constraint?

  • Pete Alberts Profile Picture
    Pete Alberts 3,542 on at

    Thanks Koen.

    I appreciate the straight forward answer.

    The part on "ax does not make "real" FK constraints on the database level. AX uses these relations internally to create lookups, do validations" - very interesting, I did not know that, thanks. I guess its something Microsoft might implement in future....... :O

    Regards

  • Pete Alberts Profile Picture
    Pete Alberts 3,542 on at

    Thanks Ievgen

    I suspect you might be confusing field fixed with related field fixed. If I'm wrong would you mind explaining - I don't fully understand.

    Regards

  • Mea_ Profile Picture
    Mea_ 60,284 on at

    Primary key on TruckTable is RecId, so FK on the TableA is RefRefRecId. You want to have RefRefRecId and RefType. 1 field on one side 2 fields on the other side.

  • Pete Alberts Profile Picture
    Pete Alberts 3,542 on at

    I assume you forgot :) docs.microsoft.com/.../conditional-table-relations

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

Season of Giving Solutions is Here!

Quick Links

Responsible AI policies

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

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 2 Most Valuable Professional

#1
Nakul Profile Picture

Nakul 2

#3
Umar-Usman Profile Picture

Umar-Usman 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans