How to find ForeignKey in SAP Dictionary tables?

Question

Table AUFK has a column called ADRNRA.

The values in column ADRNRA have matching rows in table ADRC.

In this case I found ADRC because someone told me the solution.

Maybe I am blind, but it looks like AUFK-ADRNRA is no explicit foreign key.

Is there a way to do introspection and find the matching related table (in this example ADRC) with a script or with SQL?

Answer 1

Indeed the field is not marked as foreign key of the field ADDRNUMBER of the table ADRC but should you have looked at the domain of the field ADRNRA of the table AUFK you would have found the reference in just a few seconds.

The domain (in this case AD_ADDRNUM) has an explicitly specified value table which is, surprise, surprise, ADRC.

Answer 2

Nope, there is no easy way to reveal foreign keys in an SAP system. :-(

Database management systems don't require you to model foreign keys. You only do that when you want to enforce constraints such as "must refer to a row in the other table" and automatic reactions such as cascading deletion ("delete this row if its 'parent' row in the other table is deleted").

Older SAP applications implemented constraints and reactions like these in the application layer, i.e. the ABAP code. They didn't have a need for modeled relations, and thus people simply didn't model them.

Common means to identify foreign key relationships are:

• Asking experienced users or the people who designed the database model.
• Guessing, from identical column names and data types.
• Inspecting the application code and seeing what tables it joins.

There are also some experimental machine learning algorithms that try to detect relations; google "foreign key discovery" for more information.

As @Sandra points out in her comment, newer SAP applications reveal a lot more relationships because they use CDS views. Modeling relationships there has direct benefits such as automatically generating associations in OData services, selecting along relations, and using them to model business objects.