Search on this Website

Thursday, June 5, 2008

Data Validation Using Foreign Keys


The SAP environment's relational data model can contain many tables, views, structures, and linked tables. Foreign keys, it can be said, define these relationships between multiple tables. The functions that foreign keys perform include providing help data and creating dictionary objects, but the one that you will focus on will be in the data validation area. After all, maintaining data integrity is one of the main goals when defining selection screens and the most important use of foreign keys.

A foreign key field is, by definition, restricted to values that correspond to those of the primary key of the input field's check table. This is how the link between the two tables is made. One table, FORKEY1, can be thought of as the foreign key (or dependent) table mainly because it includes foreign key fields that are assigned to primary key fields in CHECK1, which is referred to as the check (or reference) table.

Some check tables can have multiple primary key fields. In such cases, assignments must be made for each field when initiating a foreign key relationship. Three options are as follows:
Use a partial foreign key. In this case, some fields will not be a factor when validating acceptable values for entries in the foreign key field. Certain fields are flagged as generic in this case and thus ignored by the system on validation.

Use a constant foreign key. In order for the field input to be valid, the value must match that of the constant in the check table.

Create a field-to-field assignment. This is the most thorough of the three choices. Every primary key field in the check table is matched with a field in the foreign key table and all key fields are then used to determine valid entries in the foreign key table.

Basically, the way a foreign key works resembles that of a direct select statement against the check table of the field with the foreign key. More specifically, when a foreign key check field is populated, the select statement that was generated by the SAP system when it defined the foreign key is sent by the program. If the table returns a value from that selection, the entry is valid. If the record is not found, the field input is invalid.

The following code shows the syntax for a system-generated select statement:
select * from table_1 where table_1-exam1 = fk_exam1
and table_1-exam2 = fk_exam2.

This bit of code shows an example of a system-generated select statement that is called when data is entered into the field with the foreign key definition. In this scenario, an entry in this screen field is permitted only if the select statement produces valid data from the check table using the data entries made in fields fk_exam1 and fk_exam2 as keys.

Cardinality is a description of the relationship between one or more data elements to one or more of another data element. If a foreign key linking two tables has been defined, the record of the foreign key table refers to a record of the check table. This reference is constructed by assigning fields of one table, the foreign key table, to the primary key fields of the other table, the check table.

The relationship exists only if the foreign key fields are of the same data type and length as the corresponding primary key fields. A check or parent table is the table that is referenced by the foreign key itself. This is usually a value table, but it can also be a table consisting of a subset of the contents of a value table. A value table dictates the valid values that are assigned to the data element's domain.

In some cases, a constant foreign key might best suit your needs. This is the case when all valid entries in the input field contain a specific value in the key field of the cited check table. Upon the select statement's query, the constant field is checked against the primary key field that contains the fixed value.

No comments: