Defining Associations Between CDS Views

Objectives

After completing this lesson, you will be able to:

  • Define associations
  • Expose associations

Associations in CDS Views

In the previous lesson you learned, that foreign key dependencies express relationships between database tables. In a similar way, associations express relationships between CDS entities.

Associations are more flexible than foreign key dependencies. A foreign key dependency always links a foreign key table to a check table. An association can link any association source to any association target. In particular, there are usually two associations between the CDS entities where there is one foreign key dependency between two database tables. Let us have a look at out example:

The definition of database table EMPLOYEE contains a foreign key dependency for the field DEPMENT_ID, using database table DEPMENT as the check table. R_Employee and R_Department are CDS views that read from these two database tables. On CDS level, the foreign key dependency corresponds to an association (relationship) with R_Employee as source and R_Department as target. It links each employee to their respective department. However, there can also be an association (relationship) in the reverse direction, linking one department to all employees that work in this department.

Associations are defined in the data definition of the CDS entity that serves as the association source. In our example, this is the definition of CDS view R_Employee. The association is defined in the FROM clause, after the data source of the view. One CDS view definition can contain any number of associations.

Note

The data model of SAP S/4HANA contains views with over one hundred associations.

One association definition consists of the following parts:

Association target

The association to keyword is followed by the name of the association target, in our example, this is CDS view R_Department. The association target should always be a CDS entity. It is possible, but not recommended, that associations in CDS views use database tables as targets.

Association Name

The association name comes after the AS addition and serves as an identifier for the association. It is recommended that an underscore (_) is used as the first character of the CDS association name. In the example, the association name is _Department.

Note

From a technical point of view, defining an association name is optional. Without the association name, the name of the association target is used as identifier for the association. This can make it difficult to read the code and makes it impossible to have more than one association for the same target. Therefore, it is strongly recommended to define an association name for all associations.

Association condition

The association condition starts with the keyword ON. Usually, it compares view elements of the association source to view elements of the association target, but other conditions are also possible. In the recommended format, the view elements of the association source are on the left, with $projection. as prefix, and the view elements of the association target are on the right, with the association name as prefix.

Note

As it is not necessary to include the client field in the element list of the CDS views, the client field does also not appear in the association condition.

Hint

When you define an association, make use of code completion (Strg + Space), to insert the keywords, the name of the association target and the view elements.

Cardinality of Associations

The cardinality of an association specifies the number of possible data sets in the association target for one data set in the association source.

In ABAP CDS, the cardinality is specified in a pair of square brackets immediately after the keyword association.

The cardinality is specified as [min..max] or just [max]. If min is omitted, the value min = 0 is used.

Allowed values for min are 0 and all positive integer numbers. The most common values for min are 0 and 1.

Allowed values for max are all positive integer numbers and the asterisk sign (*) for an unrestricted maximum number. the most common values for max are 1 and *.

Note

If you do not specify a cardinality, the default cardinality [0..1] is used.

The syntax check performs a plausibility check for the maximum value of a cardinality. For this it compares the ON-condition to the key of the association target. If the ON-condition fully qualifies the primary key, cardinality0..1 or 1..1 is expected.

In the example, cardinality 0..* for association _Department leads to a syntax warning because the association target (R_Department) has only one key field (Id) which is qualified in the ON-condition.

This example shows the association in the opposite direction. Here, cardinality 1..1 for association _Employee leads to a syntax warning because the key field EmployeeID of the association target R_Employee is not qualified in the ON-condition.

Hint

In complex data models, it can happen that a cardinality is correct, even though it does not match the ON-condition. In such a situation, you can simply ignore the warning or suppress it using a pseudo-comment.

Exposed Associations

When you define a new association, the editor displays a warning that the association is not used. There are two ways to use an association: You can either define a path expression with this association (see later) or expose it.

You expose an association, by adding the association name to the element list of the CDS view. This makes the association visible for the consumer of the view.

Note

When you expose an association, it is mandatory that all elements of the association source, used in the ON-condition, are part of the field list. This prerequisite is automatically fulfilled, when you used the $projection prefix in the ON-condition.

Exposed Associations in Data Preview

If a CDS View definition contains exposed associations, you can evaluate these associations in the Data Preview tool as follows:

  1. Open the CDS View in Data Preview.

  2. Choose one of the displayed rows, then click on the arrow next to the view name in the header toolbar.

    Hint

    Alternatively, you can right-click the entry and choose Follow Association.

  3. Choose an association from the list of available associations.

How to Define and Expose an Association

Play the video to see how to define and expose an Association.

Log in to track your progress & complete quizzes