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.

Define a Foreign Key Relationship

You define a second database table for departments and define the foreign key relationship between departments and employees.

Template:

  • /LRN/EMPLOY_DEP (Database Table Definition)

Solution:

  • /LRN/DEPMENT_REL (Database Table Definition)
  • /LRN/EMPLOY_REL (Database Table Definition)
  • /LRN/DEPMENT_HEAD (Data Element)
  • /LRN/DEPMENT_ASSISTANT (Data Element)

Prerequisites

For this exercise, you need the database table for employee data that you created and filled in a previous exercise (suggested name was: Z##EMPLOY, where ## is your group number). If you have not finished that exercise, create a copy of database table /LRN/EMPLOY_DEP.

Task 1: Define a Database Table for Department Data

Create a database table definition to store department data (suggested name: Z##DEPMENT, where ## is your group number).

In addition to the client field, add the following fields:

Field NameData Element
idZ##_DEPARTMENT_ID (created in the previous exercise)
description/LRN/DEPMENT_DESCRIPTION

Add the id field to the table key and include the /LRN/S_ADMIN structure for administrative data into the field list.

Steps

  1. Create a new development object of type Database Table (suggested name: Z##DEPMENT).

    1. In the Project Explorer, expand your package and open the context menu on subnode Dictionary.

    2. Choose New → Database Table.

    3. Confirm the package, enter the name Z##DEPMENT and the description Department, then choose Next.

    4. Assign the new object to a transport request and choose Finish.

    5. The code of the database table definition should look like this:

      Code Snippet
      Copy code
      Switch to dark mode
      123456789101112
      
@EndUserText.label : 'Department'
@AbapCatalog.enhancement.category : #NOT_EXTENSIBLE
@AbapCatalog.tableCategory : #TRANSPARENT
@AbapCatalog.deliveryClass : #A
@AbapCatalog.dataMaintenance : #RESTRICTED
define table z##depment {

  key client : abap.clnt not null;

}

  2. After the client field, add a second key field id, and type it with the your data element Z##_DEPARTMENT_ID.

    Note

    If you have not completed the corresponding exercise, use data element /LRN/DEPARTMENT_ID, instead.

    1. Adjust the code as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      1234567
      
define table z##depment {

  key client      : abap.clnt not null;

}

  3. Add a field description based on the /LRN/DEPMENT_DESCRIPTION data element.

    1. Adjust the code as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      12345678
      
define table z##depment {

  key client              : abap.clnt not null;
  key id                  : z##_department_id;

}

  4. Include structure /LRN/S_ADMIN with administrative fields.

    1. Adjust the code as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      123456789
      
define table z##depment {

  key client              : abap.clnt not null;
  key id                  : z##_department_id;
      description         : /lrn/depment_description;

}

  5. Maintain the technical table settings. Use the value help to choose appropriate values for the data class and the size category.

    Note

    We expect the table to contain less than 100 entries.

    1. Right-click anywhere in the editor and choose Open Others → Technical Settings.

    2. Choose APPL0 Master Data, Transparent Table.

    3. Choose 0 Expected records 0 - 2.600.

  6. Activate the technical table settings and the database table definition.

    1. While still on the Technical Table Setttings dialog, press Ctrl + F3 to activate the technical table settings.

    2. Return to the editor with the source code of the database table definition and press Ctrl + F3 to activate the database table definition.

Task 2: Define a Foreign Key Relationship

Define a foreign key relationship between your database tables for employee data and department data. The relationship should document that employees can only be assigned to existing departments. Or in other words, in the department_id field of the Z##EMPLOY table, only those values are allowed for which an entry exists in table Z##DEMENT. Choose a suitable cardinality to document that any number of employees can be assigned a single department.

Steps

  1. Identify the foreign key table and navigate to the check field.

    1. Open the definition of your database table for employee data (suggested name was Z##EMPLOY) .

    2. Scroll down to the department_id field.

  2. Add a foreign key to the check field.

    Hint

    Use code-completion where possible.

    1. In the definition of the department_id field, place the cursor before the closing semi-colon (;) and press Enter to insert a new code row.

    2. Place the cursor at the beginning of the new row and press Ctrl + Space to invoke code-completion.

    3. From the suggestion list, choose with foreign key - keyword.

    4. Enter a blank space and press Ctrl + Space to invoke code completion again.

    5. From the suggestion list, choose your database table for department data, that is entry z##depment - table.

    6. Enter a blank space and use code-completion to enter the WHERE keyword.

    7. Keep using code completion to add the assignment of key fields (on the left-hand side) and foreign key fields (on the right-had side).

  3. Save the database table definition to invoke auto-formatting.

    Note

    When saving the definition, the editor also adds annotation @AbapCatalog.foreignKey.screenCheck.

    1. Press Ctrl + Space to save the development object.

    2. The code should now look like this:

      Code Snippet
      Copy code
      Switch to dark mode
      12345
      
 entry_date      : z##_entry_date;
 @AbapCatalog.foreignKey.screenCheck : false
 department_id   : z##_department_id;

  4. Add a cardinality to document that a single department ID can be used in any number of employee data sets, and that on the other hand, every employee should be assigned to an existing department.

    1. Adjust the code as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      12345678
      
 entry_date      : z##_entry_date;
 @AbapCatalog.foreignKey.screenCheck : false
 department_id   : z##_department_id
   with foreign key  z##depment
     where client = z##employ.client
       and id     = z##employ.department_id;

  5. Activate the database table definition.

    1. Press Ctrl + F3 to activate the development object.

Task 3: Define Department Head and Assistant

Extend your database table for department data with fields for the department head and the department assistant (suggested names: head_id and assistant_id). Define a new data element for each of the fields, but make sure that you use the same domain as in the employee_id field of your employee table. Then define a foreign key relation for each of the fields to document that only existing employee numbers are allowed.

Steps

  1. Edit the field list of your database table for department data. Before the INCLUDE statement, add the following fields and use a quick fix to create the new data elements:

    Field NameData Element
    department_headZ##_DEPMENT_HEAD
    department_assistantZ##_DEPMENT_ASSISTANT

    For both data elements, use your domain Z##_EMPLOYEE_IDas the data type.

    Note

    Only If you did not create a Z##_EMPLOYEE_ID domain, you can use the /LRN/EMPLOYEE_ID domain, instead. Make sure the key field ID of your database table for employee data (suggested name was Z##EMPLOY) is based on the same domain.

    1. At the end of the element list, enter department_head : z##_depment_head; (with ## being your group number).

    2. Open the Problems view. You should see an error Field DEPARTMENT_HEAD: Component type or domain used not active or does not exist.

    3. Place the cursor onz##_depment_head, and press Ctrl + 1 to open the quick fix menu.

    4. From the quick fix menu, double-click Create data element ....

    5. Confirm the package and the name, enter a description, for example, Department Head, and choose Next.

    6. Assign the new object to a transport request and choose Finish.

    7. Under Type Name, enter Z##_EMPLOYEE_ID and enter the following field labels:

      FieldValue
      Short

      Dep. Head

      Medium

      Department Head

      Long

      Department Head

      Heading

      Department Head

    8. Activate the new data element and return to database table Z##DEPMENT.

    9. In a new code line, enter assistant_id : Z##_depment_assistant; and use the quick fix again to create data element Z##_DEPMENT_ASSISTANT using the same domain, but with the following field labels:

      FieldValue
      Short

      Assistant

      Medium

      Dep. Assistant

      Long

      Department Assistant

      Heading

      Department Assistant

  2. For both new fields, add a foreign key relationship with table Z##EMPLOY as check table. Then save the database table definition to invoke auto-formatting.

    Hint

    Use code-completion where possible.

    1. After applying the auto-formatting, the code should look like this:

      Code Snippet
      Copy code
      Switch to dark mode
      123456789
      
define table z##depment {
  key client    : abap.clnt not null;
  key id        : z##_department_id not null;
  description   : /lrn/depment_description;

  include /lrn/s_admin;
}

  3. Add a cardinality to both foreign key relationships. Choose the cardinality based on the following information:

    • Not every employee can be a department head or a department assistant.
    • The same employee cannot be a head of more than one department.
    • The same employee cannot be an assistant of more than one department.
    • Each department needs exactly one department assistant.
    • A department could well do without a department head.

    1. Adjust the code as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      12345678910111213
      
  @AbapCatalog.foreignKey.screenCheck : false
  head_id      : z##_depment_head
    with foreign key  z##employ
      where client      = z##depment.client
        and employee_id = z##depment.head_id;

   @AbapCatalog.foreignKey.screenCheck : false
   assistant_id : z##_depment_assistant
    with foreign key  z##employ
      where client      = z##depment.client
        and employee_id = z##depment.assistant_id;

  4. Activate the database table definition.

    1. Press Ctrl + F3 to activate the development object.

Task 4: Fill Tables

Fill your new database table with some department data and update the employee table. To do so, create a copy of ABAP class /LRN/CL_S4D430_CHECK_AND_FILL and adjust the values of constants c_version, c_employ_table and c_depment_table. Then activate and execute the class as a console app.

Steps

  1. Create a copy of the ABAP class /LRN/CL_S4D430_CHECK_AND_FILL, name it Z##_S4D430_CHECK_AND_FILL and place it in your package ZS4D430_##.

    Note

    Skip this step if you created a copy of /LRN/CL_S4D430_CHECK_AND_FILL in an earlier exercise.

    1. In the Project Explorer view, locate the ABAP class in package /LRN/S4D430_EXERCISE and right-click on it to open the context menu.

    2. From the context menu, choose Duplicate.

    3. Enter the name of your package, the name for the copy and a description. Then choose Next.

    4. Assign the new object to a transport request and choose Finish.

  2. Change the value of constant c_version from lcl_generator=>employee_table_only to lcl_generator=>with_relationships.

    Hint

    You can add a comment sign in front of the current definition and remove the comment sign from the second definition alternative.

    1. Adjust the code as follows:

      Code Snippet
      Copy code
      Switch to dark mode
      12345
      
* CONSTANTS c_version TYPE t_version VALUE lcl_generator=>employee_table_only.
 CONSTANTS c_version TYPE t_version VALUE lcl_generator=>with_relationships.
* CONSTANTS c_version TYPE t_version VALUE lcl_generator=>with_extensions.

  3. Change the values of constants c_employ_table and c_depment_table to the names of your database tables.

    1. Scroll down to the code row starting with CONSTANTS c_employ_table.

    2. If the literal after VALUE still contains ##, replace it with your group number.

    3. Scroll down to the code row starting with CONSTANTS c_depment_table.

    4. In the literal after VALUE, replace ## with your group number.

  4. Activate the ABAP class and execute it as a console app. If the console output contains errors, adjust the definition of your database tables until they are compatible with database table definitions /LRN/EMPLOY_REL and LRN/DEPMENT_REL.

    1. Press Ctrl + F3 to activate the development object.

    2. Press F9 to execute the ABAP class as console app.

    3. Make sure the console output contains the text Filled table ... for both database tables.

Log in to track your progress & complete quizzes