Defining Relationships Between Database Tables

Objectives

After completing this lesson, you will be able to:

  • Understand relationships in data models
  • Define foreign key dependencies in dictionary tables

Relationships in Data Models

Usually, the entities of a data model are related to each other. Defining a data model includes defining these relationships.

Let's have a look at the data model of a company, which includes entities Employee and Department. The two entities are related because employees are assigned to departments.

But the two entities can share other relationships, too. For example, each department is lead by one of the employees.

Important characteristics of a relationship are its cardinalities or multiplicities. They tell you how many instances of one entity belong to an instance of the other entity.

There are different notation systems for cardinalities. The Unified Modeling Language (UML), for example, uses a maximum and minimum value, with * for an unrestricted maximum.

Let us have a look at our examples:

Every employee has to be assigned to exactly one department. Therefore the cardinality in this direction is 1..1. In the other direction, a department can have any number of employees assigned to it, even zero is allowed. Therefore, the cardinality is 0..*.

For the other relationship, let us consider that each department is led by exactly one employee, not more, not less. That means that we ignore special cases like vacant lead positions (Zero is an allowed value) or dual or multiple leadership (values greater than 1 are allowed). In the other direction, we can easily say that not every employee is a leader, which means the minimum is zero. We decide to set the maximum to 1, by which we express, that one employee can not lead two departments at the same time.

Note

In ABAP Dictionary and ABAP CDS, cardinality and multiplicity are used as synonyms. Strictly speaking, a cardinality is the actual number of instances, while the multiplicity specifies the range of allowed values.

ABAP CDS and ABAP Dictionary use different concepts to express relationships:

Associations

ABAP CDS uses Associations to define relationships between CDS view entities. Usually, one relationship is reflected by two associations, pointing in opposite directions.

Foreign Keys

ABAP Dictionary defines a dependency for table fields to establish a relationship to another database table. One such foreign key dependency corresponds to one relationship.

Let us explore the possibilities for defining relationships by having a closer look at foreign key dependencies in the ABAP dictionary.

Foreign Key Definitions in Dictionary Tables

In the ABAP Dictionary, you can use foreign key dependencies to define relationships between tables.

In modern ABAP programming, the main purpose of these foreign key dependencies is documentation. In classical ABAP development, however, they had a direct influence on the user Interface. They gave rise to implicit input checks and generated value helps.

Note

In modern ABAP development, associations between CDS view entities have partly taken over this task. See the next lesson for more details.

It is important to note that foreign key definitions in the ABAP Dictionary remain on the ABAP layer. They are not passed on to the database. As a consequence, they do not lead to implicit consistency checks on the database.

A foreign key dependency defines a relationship between two dictionary database tables, one of them is called the foreign key table and the other the check table.

A complete foreign key dependency links each primary key field of the check table to a corresponding field in the foreign key table. In the example, the primary key of the check table consists of three fields: C1, C2, and C3. The foreign key relation links them to fields F1, F2, and F6 in the foreign key table. Fields F1, F2, and F6 together form the foreign key.

Note

A foreign key can comprise any combination of fields; key fields and non-key fields. The related fields in the check table are always key fields.

The foreign key dependency restricts the values in the foreign key fields: Only those combinations are allowed, that are present in the check table. This restriction is particularly important for the last foreign key field, that is, the field that is linked to the last primary key field of the check table. This field is called the check field. In our example, the check field is field F6.

Example: Foreign Key Relationship

Play the video to see an example for a foreign key relationship.

Semantic Attributes of Foreign Keys

When you define a foreign key relationship, you can add semantic information to describe the relationship further and influence the way it is used by the framework.

The majority of semantic information is specified using foreign key annotations. Only the cardinality is specified in the DEFINE TABLE statement directly.

If a field has a foreign key relationship, you can add the following foreign key annotations:

Short Description

The short text is used as an explanatory text for a foreign key relationship.

Foreign Key Field Type

Annotation @AbapCatalog.foreignKey.keyType describes the meaning of the foreign key fields in the foreign key table. Choose value #KEY, if all foreign key fields are key fields of the foreign key table. Choose #NON_KEY if at least one foreign key field is not a key field of the foreign key table. In the example, #NON_KEY is correct, because field DEPARTMENT_ID is not a key field of table EMPLOYEE. The third value, #TEXT_KEY, is relevant if the foreign key table is a text table, that is, contains translatable text for the check table.

Value Check for Classical UI

Annotation @ABAPCatalog.foreignKey.screenCheck controls whether classical user dialogues perform value checks based on this foreign key relationship. If set to true, further annotations are available to specify a dedicated error message. Otherwise a generic error message is displayed.

Note

The screen and message annotations are not relevant in modern user dialogues. Unless you plan to develop classical UIs set the value of @ABAPCatalog.foreignKey.screenCheck to false.

The cardinality describes a foreign key relationship with regard to the number of possible dependent records (records of the foreign key table) or referenced records (records of the check table).

In the ABAP Dictionary, the cardinality is specified with [ n , m ] immediately after the keyword KEY.

  • The left side (n) specifies the number of foreign key table records per check table record and can have the values 1, 0..1, 1..* and 0..* .
  • The right side (m) specifies the number of check table records per foreign key table record and can have the values 1 or 0..1.

To choose the correct cardinality, start with the right-hand side.

Choose m = 1, if the check field is a mandatory field. In this case, each record in the foreign key table requires a value in the check field and a matching record in the check table.

Choose m = 0..1, if the check field is optional. Then records without a value in the check field do not require a matching record in the check table.

Let's have a look at our example with employees and departments, again:

If it is mandatory that an employee is assigned to a department, the cardinality of the foreign key should have m = 1. If it is also possible that an employee is not assigned to a department, the cardinality should have m = 0..1.

Now let's have a look at the left-hand side of the cardinality. The meaning of the different values is as follows:

  n = 1

There is exactly one dependent record (record of the foreign key table) for each record of the check table.

n = 0..1

There is at most one dependent record for each record of the check table. But there are records of the check table for which there is no dependent record in the foreign key table.

n = 1..*

There is at least one dependent record for each record of the check table.

n = 0..*

There can be any number of dependent records for each record of the check table: none, one, or more than one.

In our example you have to ask yourself: How many employees can be assigned to the same department? Exactly one? At most one? At least one? Any number? What do you think?

How to Define a Foreign Key Relationship

Play the video to see how to define a foreign key relationship.

Log in to track your progress & complete quizzes