Foreign Key in SAP ABAP

What is Foreign Key?

• Foreign keys are used to establish the relationship between the different tables present in the ABAP Dictionary.
• We can create value checks for input fields using the foreign keys. Value checks are required to validate the values of some fields in a table with the values of other fields of different tables.
• The table that contains the foreign key is called a foreign key table and another table that contains valid fields, known as the value table.
• It connects two tables by assigning the foreign key field of one table to the primary key field of another table.
• The below diagram shows the assignments of fields using a foreign key.

Below are the two requirements for creating a foreign key relationship:

• The fields used in the check table for the validation must be the primary keys.
• The foreign key fields in the value table and primary key fields in the check table must be of the same domain so that it can ensure that both fields are compatible with data type and length.

What is a Primary Key?

A primary key is a field that uniquely identifies the rows in a table by one or more columns. The primary key field cannot have a NULL value or duplicate values. In order to establish the relationship between two tables or to link the tables, the primary key of the first table (referenced table) will be added to another table (dependent table), and it will become the foreign key for the second table.

Concept of Foreign key Relationship:

The foreign key relationship is about relating or connecting two different tables in order to avoid redundancy and perform table validations in ABAP.

We can understand it by an example. Suppose there are two tables; Table1(foreign key table or dependent table), Table2(Check Table or referenced table). The tables are given below:

All the records are successfully saved in Table1. And Below, we will save records in Table2.

As we can see, it has two records that are successfully saved. But if we try to store another record in this table that is not present in Table1, such as 5    Dept3       Assist. Manager

Then due to the foreign key relationship, we are not allowed to add this record in Table2.

Table validation in ABAP:

Table validation is a technique by which we can restrict the invalid entries in our table. There are two ways to perform the table validation in ABAP:

• Field Level Validation: In this method, we perform the field level validation by using the check table concept.
• Domain-Level Validations: Domain level validation can be performed by restricting entries at the domain level with the help of the value table of that domain and fixed values of the domain.

Note: Here, we will understand the Field level validation with the help of the Check table concept.

Check Table Concept(Field Level Validation)

In the Check table concept, we consider two tables; Check table and Foreign key table.

The check table is the table that stores the master data.

The other table, which is linked with the check table to validate its own field data, is called a foreign key table. The value table is developed at the domain level and primarily used for data integrity and consistency.

In the below diagram, we can understand the check table concept:

As we can see in the above diagram, there are two tables, T1(Foreign key table) and T2(Check Table). The T2 table contains two primary key fields Field5 and field 6, and the T1 table includes the two foreign key fields, Field 2 and Field 4, with one primary key Field 1.

So, we can connect the two tables with the primary keys of T2(Field 5 and Field 6) to the Foreign key fields of T1(Field 2 and field 3).

Here one point we need to remember that the data type and length of all the linked fields should be the same, then only the foreign key relationship can be maintained.

How does the input check work?

It can be understood by the understanding of the below concept:

Triggering Foreign Keys

When we enter a value in the foreign key table, immediately, the foreign key gets triggered. Triggering of the foreign key is initiated using the SELECT statement, which checks for the matching rows in the check table. If there is no matching occurred in the table, it displays a standard message which indicates that the entered value is invalid. Apart from the SELECT statement, the foreign key can be triggered using a function key, a button, or a menu item.

Below are some other concepts related to the Foreign key:

• Compound Foreign Keys
• Generic and Constant Foreign Keys
• The cardinality of Foreign Key
• Foreign key Field Type

Compound Foreign Keys:

A foreign key consisting of two or more fields is called a compound foreign key. To apply checks for compound foreign keys, the system compares two fields in the foreign key table against the two fields in the check table.

The field that defines the compound foreign key is called a check field, and it must be empty, else the compound foreign key will not get triggered.

Generic and Constant Foreign Keys

As we know, to create a foreign key, all the primary keys of the check table must be included in a foreign key relationship. But in some cases, we just want to perform validation against selected fields only, and other fields can be excluded, so for such cases, we can use the generic foreign keys.

We can also assign a constant value to the key field of the check table, which will allow us to check the values against a constant value of the check table.

Semantic Attributes of Foreign Keys

The foreign key relationship between two tables can be specified more precisely by defining the Cardinality and Type of foreign key fields. Although providing this information is optional and not used in the value check. It is mostly used for documentation purposes.

The cardinality of a Foreign Key

The cardinality of the foreign key is used to specify the allowed rows in the foreign key table for the corresponding value in the check table. It is defined while creating the foreign key relationship. It can be expressed as X: Y relationship, where X is used for the check table, and Y is for the foreign key table. 

The possible values for X can be:

• X=1: It specifies that a check table has a single record for each record of the foreign key table.
• X=C: It specifies that the records contained within the foreign table may not correspond to any record of the check table.

The values for Y can be:

• Y=1: It specifies that for each record in the check table, only one record is allowed in the foreign key table.
• Y=C: It specifies that the maximum one record of the check table can exist for the foreign key table.
• Y=N: It specifies that for each record of the check table, at least one record of the foreign key table should exist.
• Y=CN: It specifies that any number of independent records of the foreign key table may exist for each record of the check table.

Example: If the cardinality is given as 1:N, it means there is exactly one record that exists for each record of the foreign key table.

Foreign Key Field Type

Foreign key field type describes the meaning of the foreign key field in the foreign key table. Below are the different types of foreign key field:

Creating Foreign keys in ABAP

Below are the steps to create the foreign key relationships in ABAP database tables:

• Open the Data Dictionary initial screen by entering the SE11 transaction code or by following the menu path.
• Now create the first database table as Check table.
• Next, we need to create another database table, let's say it "ZEMPLOYEES" that contains three main fields, which are, EMPID, FULLNAME, and DOB.
• Once both the tables are created successfully, we can now create the foreign key relationships between the tables, follow the given steps:
  • First, open the second table, i.e., "ZEMPLOYEE."
  • Select the EMPID field from the table.
  • Now click on the foreign keys button given on the screen. Consider the below screen:
    
  • New windows will pop-up, where provide the Check table name, which is containing the master data. Consider the below image:
    
  • Now at the below screen, you can see the option of Foreign key field type, which is containing four options. We have already explained these options in the above section.
  • Now click on the Generate proposal button, and then click to the copy button.
  • At last, click to save and activate it.
• Click on Unit testing to check whether it is working properly or not, so below are the steps for this:
  • Go to the Utilities option in the table-> table contents-> Create.
  • Enter some different name or a wrong name that is not present in the customer table, and click on the Save button.
  • Once we save it, an error message will be displayed to us.

Customizing Error message in the foreign key check

If the check table validation or foreign key check gets failed, a standard error message is generated. This error message can be replaced with a customized error message. It can be done by using the SE91 SAP transaction code.