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?

Testes

You're not blind, the foreign keys, redundant columns and so on, are often not defined in classic ERP tables. In SAP world, everyone has its own cheat sheet, or we ask the question. I think that the situation has changed a lot with CDS. – Gyrostabilizer

@SandraRossi I like ForeignKeys. This is a new entry in my comparison which I create while learing abap/sap: github.com/guettli/why-i-like-django-and-sap/blob/master/… – Testes

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.

Gerome

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.

Intosh

I guess an other reason are compound keys. In SAP compound primary keys are common. Most tables have a column MANDT and AFAIK in most cases it is part of the primary key. This means the foreign key needs to be compound, too. In the past I worked with the web framework django. It does not support compound keys. This makes things much simpler. – Testes