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.
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.
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.