Day 4

The Data Dictionary, Part 2


Chapter Objectives

After you complete this chapter, you will be able to:

Discovering Foreign Keys

A foreign key is a field in a table that is connected to another table via a foreign key relationship. The purpose of the foreign key relationship is to validate the data being entered into one table by checking against a valid set of values in another table. The table that contains the foreign key is called the foreign key table. The table that contains the valid set of values is called the check table (see Figure 4.1). The field that is validated and on which the foreign key relationship is created is called the foreign key.

Figure 4.1 : The foreign key table and the check table.

For example, your -lfa1 table contains country codes in a field named land1. You can create a second table, for example -t005, and fill it with a list of valid country codes. Then, if you create a foreign key like the one shown in Figure 4.1, a check will automatically be done against the check table for a valid value. With such a foreign key in place, the user cannot insert invalid country codes into table -lfa1.

A foreign key gets its name from the fact that it must always check against a field in the primary key of another table.

Triggering the Foreign Key Check

Foreign key checking is performed by the R/3 user interface. When you type a value into a field and press the Enter key, behind the scenes the user interface formulates a select statement to validate the foreign key field and sends it to the database. If a matching row is not found in the check table, the user sees a standard message indicating that the value he entered is invalid. If a matching row exists in the check table, the values entered on the screen are allowed to pass to the underlying ABAP/4 program. Thus, the foreign keys are already checked before the program receives the values. In addition to the Enter key, foreign keys are also checked when a function key, a pushbutton, or a menu item is selected.

Foreign keys are checked by the user interface only. They do not exist in the database, and they are not checked if you merely issue a database update statement. No referential integrity constraints are kept in the database. Therefore, an incorrectly written ABAP/4 program can violate referential integrity; it can insert invalid values into database tables. For that reason, ABAP/4 programs that need to update the database with data that comes from a source other than the user interface usually pass that data through a user interface in order to validate it.

NOTE
For more information on using the user interface to validate data, refer to the R/3 Library help for BDC (Batch Data Communication) programs. To view this documentation, from any screen choose the menu path Help->R/3 Library. You will see the main menu of the R/3 library. From there, click on the following tabs: Basis Components->ABAP/4 Development Workbench-> Basis Programming Interfaces->Transferring Data With Batch Input.

Technical Requirements for Creating a Foreign Key

To create a foreign key:

The second requirement exists to make sure that the fields being compared are compatible in data type and length (see Figure 4.2).

Figure 4.2 : This is a diagram of the foreign key field, check table field, and the common domain they share.

NOTE
In Figure 4.2, both fields use the same data element as well as the same domain. Notice that it is not required that they both use the same data element-only the domains must be the same.

Because these fields share a common domain, the integrity of the comparison between the two fields is guaranteed because their data types and lengths will always match.

Creating a Foreign Key

Now it is time for you to create a simple foreign key. This example will enable you to become familiar with the screens involved in this process; all of the fields in those screens will be explained in detail later in this chapter. The following procedures demonstrate how create check table ztxt005, and then a foreign key on ztxlfa1-land1 by using ztxt005 as the check table. After viewing this procedure, you will be asked to create your own -t005 table and then create a foreign key relationship from -lfa1-land1 to check table -t005.

Before you create a foreign key, you must first have a check table. Although there is already a country code table in R/3, for practice you will create and use your own. The check table will be named -t005 and will contain the fields and use the data elements shown in Table 4.1. (An x in the PK column indicates fields that form the primary key.)

Table 4.1  The Fields and Their Characteristics for Table -T005
Field Name
PK
DE Name
mandt
x
mandt
land1
x
-land1

Check Table Creation Procedure

Following is the creation procedure for check table -t005. If you have trouble, refer to the troubleshooter for the transparent table creation procedure for pre-existing domains and data elements on Day 3.

Start the ScreenCam "How to Create Check Table -t005" now.

To create your check table:

  1. Begin at the Dictionary: Initial Screen.
  2. Type the table name in the Object Name field.
  3. Choose the Tables radio button.
  4. Press the Create button. The Dictionary: Table/Structure: Change Fields screen is displayed.
  5. Type a short text, choose delivery class A, and tickmark Tab. Maint. Allowed.
  6. Type the field and data element names at the bottom of the screen.
  7. Tickmark both fields to indicate that they both form the primary key.
  8. Press the Save button on the Standard toolbar. The Create Object Catalog Entry screen appears.
  9. Press the Local Object button. You are returned to the Dictionary: Table/Structure: Change Fields screen.
  10. Press the Technical Settings button on the Application toolbar. The ABAP/4 Dictionary: Maintain Technical Settings screen is displayed.
  11. In the Data Class field, enter APPL0 (APPL0 with a zero, not APPLO with an alphabetic O).
  12. Enter 0 (zero) in the Size Category field.
  13. Press the Save button.
  14. Press the Back button. You are returned to the Dictionary: Table/Structure: Change Fields screen.
  15. Press the Activate button on the Application toolbar. The message "was activated" appears in the status bar and the Status field contains the value Act..

Choose the menu path Utilities->Create Entries to enter data into your newly created check table. Enter six rows with land1 values of US, CA, DE, IT, JP, and AU.

Foreign Key Creation Procedure

Start the ScreenCam "How to Create a Foreign Key" now.

To create a foreign key:

  1. Begin at the Dictionary: Initial Screen.
  2. In the Object Name field, type the name of the table that is to contain the foreign key (-lfa1).
  3. Choose the Tables radio button.
  4. Press the Change button. The Dictionary: Table/Structure: Change Fields screen is displayed.
  5. Position your cursor on the field that is to become the foreign key (land1).
  6. Press the Foreign Keys button on the Application toolbar. The Create Foreign Key screen will be displayed (see Figure 4.3).
  7. Type a description of the foreign key in the Short Text field, for example, Country Code validation.
  8. Type the name of the check table (-t005) in the Check Table field, or click on the down-arrow and choose it from the list.
  9. The Change Foreign Key pop-up appears. It states Check table name was created or changed. Generate a proposal for field assignment? Press the Yes button.
  10. You are returned to the Create Foreign Key screen. The check table field names and the foreign key field names are displayed. (The check table field names are -t005-mandt and -t005-land1. The foreign key field names are -lfa1-mandt and -lfa1-land1).
  11. Press the Copy button. You are returned to the Dictionary: Table/Structure: Change Fields screen and the message "Foreign key was transferred" appears in the status bar. The Status fields contain the values Revised and Not saved.
  12. Press the Activate button on the Application toolbar. The message "was activated" appears in the status bar. The values in the Status fields are now Act. and Saved.

Figure 4.3: The Create Foreign Key screen displays check table fields and their associated foreign key fields.

You have just created a foreign key relationship between the -lfa1 and -t005 tables. -lfa1-land1 is the foreign key field, and -t005 is the check table. The user interface will validate the value within the -lfa1-land1 field against the values in the -t005-land1 column.

Testing the Foreign Key Relationship

If you encounter problems, the following procedure is described in more detail in the next section. To test your foreign key:

  1. Begin on the Dictionary: Table/Structure screen (either change or display mode).
  2. Choose the menu path Utilities->Create entries.
  3. Type a value into the foreign key field (Country Code).
  4. Press the Enter key. If the value is not found in the check table, the message "Entry does not exist (please check your entry)" is displayed.

Discovering the CheckTable Column

After you have created a foreign key, the name of the check table appears in the CheckTable column on the Dictionary: Table/Structure screen. If a field is a foreign key, the CheckTable column will contain the name of the check table for that field.

Look at your table now. You should see the name of your check table in the CheckTable column.

Automatic Determination of Foreign Key Fields

When you created the foreign key, you did not have to specify the fields that should participate in the foreign key relationship-the system determined it automatically. This section explains how the system determines these field names.

During the creation of a foreign key, you put your cursor on the foreign key field and pressed the foreign key button, and the system generated a foreign key proposal consisting of pairs of fields. In each pair, one field comes from the check table and the other from the foreign key table. The number of pairs is equal to the number of primary key fields of the check table. All of the primary key fields of the check table must be included in the foreign key relationship.

The system searches these primary key fields for the one that has the same domain as the foreign key field, and pairs it with your foreign key field.

If there are multiple fields in the primary key of the check table, it will try to find a match for each of the remaining fields. One by one, the system will try to find a field in the foreign key table that has the same domain as the check table field. Failing that, it will try to find a field that has the same data type and length. If it finds more than one match, the system picks one and warns you that the assignment is ambiguous. The process is repeated until all primary key fields from the check table have been paired with a field from the foreign key table.

Relating Foreign Keys to the Down-Arrow and F4 Help

In addition to F1 help, you can also provide something called F4 help to the user. F4 help is a list of valid values that the user can enter into a field. In order to obtain F4 help, the user positions their cursor in a field and presses the F4 key. F4 help is also known as possible entries help because it is the list of possible entries allowed in the field. Both terms are used interchangeably by SAP.

When you create a foreign key, F4 help is automatically provided for that field.

Start the ScreenCam "Using a Foreign Key to Provide F4 Help" now.

On a screen, if you place your cursor within a foreign key field, a down-arrow will appear at the end of the input field. This is the F4 help. If you click the down-arrow or press the F4 key, a list of possible, valid entries is displayed. This list is the contents of the check table. In this list, the primary key columns of the check table are displayed, excluding mandt.

NOTE
The column heading and width in the possible entries list come from the Header field in the data element for the check table field displayed.

Follow this procedure to test the foreign key relationship you created on -lfa1-land1. If your foreign key doesn't work, try this procedure using ztxlfa1, then compare ztxlfa1 with your table to determine the problem.

  1. Begin on any screen.
  2. In the Command field, type /nse16.
  3. Press the Enter key. The Data Browser: Initial Screen is displayed.
  4. Type -lfa1 in the Table Name field.
  5. Press the Create Entries button on the Application toolbar. The Table Insert screen is displayed.
  6. Type any value in the vendor number field, for example, MY-V1.
  7. Position your cursor on the Country Code field (land1). Although a down-arrow appears at the end of it, do not press it just yet.
  8. Type the value XX in the Country Code field.
  9. Press the Save button on the Standard toolbar. The message "Entry XX does not exist (please check your entry)" appears.
    Your cursor is positioned at the beginning of the field that contains the incorrect value. A down-arrow appears at the end of the field.
  10. Click the down-arrow or press the F4 key. A pop-up containing the primary key columns of check table -t005 appears.
  11. Double-click US. The pop-up disappears and the value in the country code field changes to US.
  12. Press the Save button on the Standard toolbar. The message "Database record successfully created" appears in the status bar.

The foreign key relationship provides a mechanism for validating data entered by the user on the screen. If the field is a foreign key field, the value is compared against the check table. If the value doesn't exist in the check table, a standard error message is issued.

Foreign Keys and Batch Programs

Batch programs that read sequential files or other sources of data other than the screen and update the database directly must validate the data. Such a program should be coded in one of three ways:

Compound Foreign Keys

A compound foreign key is a foreign key composed of two or more fields. In other words, a check is done to compare two fields in the foreign key table against two fields in the check table. The combination of values must exist in the check table before they can be inserted into the foreign key table. However only one field on the screen will trigger the compound foreign key check.

When you create a compound foreign key, the field on which you actually define the foreign key is called the check field. Only a non-blank value in the check field triggers the check against the check table. A value in any of the other fields within the compound foreign key will not trigger the validation.

For example, the regio field in ztxlfa1 should only contain valid state or province codes. The combination of regio and land1 together must be valid. Therefore, a check table (ztxt005s) containing valid combinations of regio and land1 is needed. After creating this check table, a compound foreign key is needed in ztxlfa1 to validate the combination of regio and land1 against the check table. Incidentally, ztxt005s-land1 should also be validated against ztxt005-land1 (see Figure 4.4).

Figure 4.4: A compound foreign key validating the combination of region and country against the check table.

You create a compound foreign key almost exactly the same way as a foreign key containing a single field. You begin by positioning your cursor on a field, in this case ztxlfa1-regio, and then pressing the Foreign Keys button. Because your cursor is on regio, it becomes the check field of the compound foreign key. Although the foreign key is composed of multiple fields, only one of those fields will trigger a check against the database. Because regio is the check field, a value entered into it on the screen triggers a check. If the check field is non-blank when the user presses the Enter key, a check is carried out against the check table comparing all fields of the foreign key. If the check field is blank, no checking is done even if the other fields contain values.

CAUTION
As with the simple foreign key, the domain names of the check field and its associated field in the check table must match. However, when the foreign key is created, the rest of the domain names in the compound foreign key do not have to match; the foreign key can be created if only their data types and lengths match. Later, if the data type or length in one of these domains is changed, the fields will no longer match and the results of the foreign key check will be unpredictable. For this reason, although R/3 does not enforce that all domain names of a compound foreign key match, it is highly desirable.
As a rule of thumb, when changing the data type or length of field in a compound foreign key other than the check field, you must check to see whether the foreign key and check table fields are using the same domain. If they are not, you must update them both to have the same data type and length. Failure to do so will result in unpredictable operation of the foreign key.

If you create a foreign key relationship to a check table containing more than one field in the primary key, all of the primary key fields of the check table must be included in the foreign key. They do not all have to be checked (see the section "Generic Foreign Keys" that follows), but they all must be included.

Because most check tables contain the mandt field, it forms a part of most foreign keys (see Figures 4.5 and 4.6).

Figure 4.5 :A compound foreign key involving the mandt field.

Figure 4.6 : The definition of the foreign key shown in Figure 4.5.

By including mandt in the check table, independent and valid sets of values can be defined for each logon client and can differ between clients. This capability is highly desirable to separate the data between clients and ensure that they are completely independent of each other.

F4 help is only available on the check field of a compound foreign key. When invoking F4 help, all of the primary key fields of the check table will be displayed. The column containing the check field will be highlighted in the list. The column widths and titles come from the Header field in the data elements.

Understanding Cardinality

The cardinality of the foreign key relationship describes, for each value in the check table, how many rows of data are allowed to be in the foreign key table. It is entered on the Create Foreign Key screen (refer to Figure 4.3). Cardinality is described as X:Y, where X refers to the check table and Y refers to the foreign key table. X can only have the values 1 or C, and Y can be 1, C, N, or CN.

The values of X mean:

The values of Y mean:

Cardinality is not enforced by the R/3 system. Specifying cardinality is optional, and the system will not check cardinality to determine whether that update should be allowed to take place if an ABAP/4 program updates a table. The only time cardinality is used is when you create an aggregate object in the DDIC.

An aggregate object is a DDIC object composed of more than one table. An example of an aggregate object is a view.

When you create a foreign key you should specify the cardinality. If you don't specify it, you will be unable to include the table in an aggregate object.

Foreign Key Field Type

The foreign key field type should also be specified on the same screen as the cardinality when you create the foreign key (refer to Figure 4.3). It can be one of the following values:

Key Fields

Choose Key Fields/Candidates if the foreign key field is one of the primary key fields of the foreign key table. Choose Non-Key Fields/Candidates if it is not. For example, assume table t1 is composed of fields f1 through f10, and its primary key fields are f1, f2, and f3. If you were to define a foreign key on field f3, Key Fields/Candidates would be the correct type because f3 is part of the primary key. If you were to define a foreign key on field f5, Non-key Fields/Candidates would be correct because f5 is not one of the primary key fields.

Key Candidates

A key candidate is a field in a table that, by itself or in conjunction with another field that is not part of the primary key, could be used to uniquely identify a row in the table. To put it another way, if there is way to uniquely select a row from a table without using primary key fields, key candidates exist in that table. The key candidates are the fields not within the primary key that can be used to uniquely identify a row; in essence they can serve as an alternate primary key.

For example, ztxlfa1 has a single primary key field that contains a unique identifier for each row. If you assume that the combination of erdat and ertim (creation date and time) is also unique for every row, these date and time fields are key candidates. If either one appeared in a foreign key, the correct foreign key type would be key fields/candidates.

When creating a foreign key, you should choose key fields/candidates if the foreign key field is one of the primary key fields of the table or is a key candidate. Choose non-key fields/candidates if it is not.

Key Fields of a Text Table

The R/3 system supports multiple spoken languages, and thus enables users to sign on using the language of their choice. Because of this, descriptions in R/3 are stored in special language-dependent tables called text tables.

TIP
Users can set their own default logon language. This is done in the user profile by choosing the menu path System->User profile->User defaults. Changes to your user profile are not effective until you next log on.

A text table is a table that contains spoken-language descriptions of values in a check table. These descriptions are stored in multiple languages. The primary key of the text table is the same as the primary key of the check table, with the addition of a spras (language) column.

For example, the ztxt005 table has country codes in it. Country names are stored in a separate table named ztxt005t (shown in Figure 4.7) because you actually need many names for one country code. Because it stores language-specific descriptions of a generalized code, ztxt005t is called a text table.

Figure 4.7 : The text table for ztxt005 is ztxt005t. It is composed of the fields mandt, spras, land1, landx, and natio.

The primary key of ztxt005t contains the same fields as the primary key of ztxt005, with the addition of a spras (language) column. The spras field contains the language code and enables this table to contain a description for multiple logon languages.

The primary key of any text table is composed of the fields mandt and spras, followed by the validation field (or fields). One or more description fields follow this primary key.

A foreign key relationship is defined on ztxt005t-land1 to check table ztxt005. The foreign key field type should be key fields of a text table.

Relating Text Tables to F4 Help

The foreign key type "key fields of a text table" indicates to the R/3 system that the foreign key table is a text table. When you choose this type, the first description that follows the primary key is displayed in the F4 help, in the logon language of the user. Therefore, this key type has two special properties: For example, text table ztxt005t contains a foreign key relationship from ztxt005t-land1 to check table ztxt005, and the type is "key fields of a text table". When you invoke F4 help on ztxt005-land1, the user sees the primary key columns of the check table ztxt005, plus the contents of the first column that follows the primary key of ztxt005t. Only the rows where spras is equal to the current logon language are displayed. The result is that the user sees descriptions in his logon language for the codes in ztxlfa1.
The contents of tables ztxt005 and ztxt005t are shown in Tables 4.2 and 4.3.

Table 4.2  Contents of Table ZTX005
land1
US
CA
DE
IT
JP
AQ
CC

Table 4.3  Contents of Table ZTXT005T
spras
land1
landx
E
CA
Canada
D
CA
Kanada
E
DE
Germany
D
DE
Deutschland
E
US
United States
D
US
USA
E
IT
Italy
D
IT
Italien
E
JP
Japan
D
JP
Japan
E
AQ
Antarctica
D
AQ
Antarctica
E
CC
Coconut Islands
D
CC
Kobinseln

Start the ScreenCam "Text Table Demonstration: The Effect of the Logon Language" now.

  1. Start transaction SE16 (type /nse16 in the Command field and press the Enter key).
  2. Enter the table name ztxlfa1 in the Table Name field.
  3. Press the Create Entries button on the Application toolbar.
  4. Position your cursor on the Country Code field (land1).
  5. Press the F4 key, or click the down-arrow at the end of the field. A pop-up appears displaying columns from both tables ztxt005 and ztxt005t. Notice that the land1 column from table ztxt005 is displayed and the English-language descriptions from table ztxt005t are displayed.
  6. Now log on again (you do not need to log off first). This time, enter D for Deutsche in the Language Field of the R/3 logon screen.
  7. Repeat steps 1 through 5 in your new logon session.
  8. Notice that this time the German descriptions from table ztxt005t are displayed.
Creating a Text Table and a Foreign Key for It
Now is a good time to try creating your own text table. Use ztxt005t as a model. Your text table will be named -t005t and will contain the fields and use the data elements and domains shown in Table 4.4. You will also have to create the data element -landx. Use the existing SAP domain text15 for your new data element. An x in the PK column indicates fields that form the primary key.

Table 4.4  Fields and Their Characteristics for Table -T005T
Field Name
PK
DE Name
DM Name
mandt
x
mandt
 
spras
x
spras
 
land1
x
-land1
 
landx
 
-landx
text15

After activating -t005t, enter data into it using SE16. Use the sample data shown in Table 4.3.

Now create a foreign key on -t005t-land1. Follow the foreign key creation procedure previously given. Use -t005 as the check table. On the Create Foreign Key screen, specify a foreign key field type of Key fields of a text table. You might want to review the screencam "How to Create a Foreign Key" before beginning.

After creating your foreign key, follow the procedure for testing the foreign key relationship on -lfa1-land1. In step 11, notice that the description from ztxt005t is also displayed. Also, log on again, this time specifying a logon language of D (German), and repeat the test. This time you should see the German descriptions in step 11.

Generic and Constant Foreign Keys

Recall that when you create a foreign key, all of the primary key fields of the check table must be included in the foreign key relationship. However at times, you may not want to perform a check against all of these fields. In other situations, you may wish to check against a constant value. In either of these cases, you can define either a generic or constant foreign key.

Generic Foreign Keys

A generic foreign key is a foreign key in which one of the primary key fields of the check table fields is marked as generic. When a primary key field is marked as generic, it doesn't have a counterpart in the foreign key table, and it does not participate in the check.

For example, within the Materials Management application, the mara table is the Material Master table. It contains the master list of materials used for manufacture and the attributes of each material.

The setup program for this book created a ztxmara table similar to mara. The fields of ztxmara are shown in Figure 4.8.

Figure 4.8 : Table ztxmara is similar to the R/3 Material Master table mara.

The ztxmara-stoff field contains a hazardous material number. If the field is non-blank, the material requires special handling; the stoff number indicates the type of handling required. The handling numbers are defined in table ztxmgef, which is illustrated in Figure 4.9. In our example, assume ztxmgef contains a row with stoff equal 1. That row indicates a handling procedure that requires gloves. Updating a material and placing a 1 in the ztxmara-stoff field for the material indicates that the material requires special handling with gloves.

Figure 4.9 :The ztxmara and ztxmgef tables relate via the stoff field.

The check table for ztxmara-stoff is ztxmgef. The foreign key definition is shown in Figure 4.10.

Figure 4.10: A tickmark in the Generic check box causes a field to be ignored during the foreign key check.

This foreign key validates the values entered into ztxmara-stoff and ensures that only valid handling numbers exist in the ztxmara table. Unfortunately, handling regulations can vary with geographical regions. Therefore, the primary key of ztxmgef is composed of stoff, regio, and land1. However, ztxmara doesn't contain any geographical information - that information is contained in plant tables.

Remember, a foreign key must include all primary key fields from the check table. Therefore, regio and land1 from ztxmgef appear in the foreign key definition. However, for the purposes of ztxmara, validating ztxmara-stoff against ztxmgef-stoff is good enough; location information doesn't exist in ztxmara and doesn't matter for this validation. It is enough to know that the specified special handling number exists.

In this foreign key relationship, the regio and land1 fields in the primary key of ztxmgef are be ignored. To cause these fields to be ignored during validation, they are defined in the foreign key relationship as generic. The tickmark in the Generic check box on the Create Foreign Key screen accomplishes this.

When this foreign key is checked, only the combination of mandt and stoff must exist in the check table. The regio and land1 fields are ignored.

Constant Foreign Keys

Constant foreign keys are compound foreign keys where one of the check table field names is replaced by a constant value. This constant value is entered on the Create Foreign Key screen (see Figure 4.11).

Figure 4.11: The country field as a constant foreign key.

For example, assume you need to create a new vendor table ztxlfa1us like ztxlfa1 containing U.S. vendors only, but for compatibility reasons, you want it to have exactly the same structure as ztxlfa1. The country code in this table would always be US. You could create a constant foreign key to restrict the values in table ztxlfa1us. An example of the definition is shown in Figure 4.11.

Adapted Foreign Keys

Foreign key fields of a compound foreign key do not all have to reside in the same table. If they do not, this is known as an adapted foreign key. For example, assume that the country code for the vendor is not stored at all in ztxlfa1, but instead in ztxlfa1cc. When you create the foreign key on the region field, you would change the check table field for land1 to ztxlfa1cc-land1 (see Figure 4.12). The validation on the regio field would check to see whether the combination of mandt and regio existed in ztxt005s and whether land1 existed in any row of ztxt005cc.

Figure 4.12: An example of an adapted foreign key.

Defining a Value Table

Within the domain is the Value Table field. The table named within this field provides the following functions to the domain:

Whenever you create a foreign key relationship, the system looks in the value table field. If there is a table name there, it proposes that name as the check table.

Some readers will have difficulty with this concept, so I will explain it in depth in the following section.

Understanding the Value Table Field

In order to understand the purpose of the Value Table field, you should be familiar with the "bigger picture" of database design and development. I will begin by describing this bigger picture.

Before you create any tables in the DDIC, you usually do some database design. This design describes the tables that will be created, their relationships, and the type of data that each will contain. It will usually designate certain tables to be check tables for validating the data within other tables. For example, a country code check table may be needed to validate country codes for all of the tables where country codes will be stored. When you create a set of tables within R/3, you will usually create the check tables first.

When you create a check table, it exists for one primary purpose: to validate data. You can create multiple foreign key relationships linking many other tables to a single check table. The Value Table field in the domain exists to simplify the task of creating these relationships.

Before I continue, I want you to imagine that the Value Table field within the domain has a completely different name. Wipe the term "Value Table" from your mind, and imagine that field is called the "Default Check Table" field.

To simplify the example, our check table will have a single primary key field, illustrated in Figure 4.13. To illustrate this concept, I will describe the process you would go through to create this check table and use it in a foreign key relationship.

Figure 4.13: The Value Table field within the domain contains the name of a check table.

When you create a check table, you must first create a domain. You would always create a new domain specifically for a check table. You would then create the data element, and then the table itself. You would then activate the table. You would then go back into the domain for the primary key field and put the name of the check table you just created in the Value Table field - but remember we call it the Default Check Table field.

Now, you would create another table, in our example it is ztxlfa1. You would create the land1 field and use the ztxland1 data element, and then both fields would use the same domain.

Now you would create the foreign key to validate land1. You would place your cursor on the ztxlfa1-land1 field and press the Foreign Key button. When you do this, the system will look in the domain at the Value Table field (remember we call it the Default Check Table field). If there were a table name there, the system would automatically propose that name as the name of the check table. In our case, the system would find ztxt005. Therefore, the system would propose ztxt005 as the check table-exactly what we would want. The foreign key proposal would be shown, and we could then save it.

In this example, the system automatically created the foreign key for us, because we put the name of the check table in the Value Table field. Using three mouse clicks, we created a foreign key! That, in essence, is the primary reason for the Value Table field. It exists to make the creation of foreign keys easier.

To summarize, the Value Table field is used in two ways by the system:

Value Table Field as the Check Table Proposal

In this section you will add a value table to a domain and observe its effect on the creation of a foreign key.

Edit domain -land1 now and enter -t005 in the Value Table field, then activate the domain.

Now you will delete the foreign key on the -lfa1-land1 field and then create it again so that you can see the effect of the Value Table field on the foreign key creation process. To do so, follow the procedure below.

  1. Begin at the Dictionary: Initial Screen.
  2. In the Object Name field, type the name of the table (-lfa1).
  3. Choose the Tables radio button.
  4. Press the Change button. The Dictionary: Table/Structure: Change Fields screen is displayed.
  5. Position your cursor on the foreign key field (land1).
  6. Press the Foreign Keys button on the Application toolbar. The Change Foreign Key screen is displayed.
  7. Press the Delete button (the trashcan) at the bottom of the window.
  8. You are returned to the Dictionary: Table/Structure: Change Fields screen. The message Foreign key was deleted appears in the status bar.
  9. With your cursor still positioned on the foreign key field (land1), press the Foreign Keys button again. The Create Foreign Key screen is displayed. It reads Foreign key does not exist. Generate proposal with value table as check table? You see this message box because the system has looked in the domain and found a name in the Value Table field. It responds by issuing this message box.
  10. Press the Yes button. The Create Foreign Key screen is displayed, and the Check Table field contains the value table from the domain. The Check Table fields and Foreign Key fields are filled in.
  11. Press the Copy button. You are returned to the Dictionary: Table/Structure: Change Fields screen. The message Foreign key was transferred appears in the status bar. The Status fields read Revised and Not saved.
  12. Press the Activate button. The message was saved appears in the status bar. The Status fields read Act. and Saved.

With a table name in the Value Table field in the domain you can quickly and reliably create foreign keys.

Rediscovering the CheckTable Column

The CheckTable column on the Dictionary: Table/Structure screen performs two functions:

There is an exception to the second point above. If you are currently displaying a value table, the asterisk will not appear in the field whose domain references the value table you are viewing.

Using the Value Table as a List of Possible Values

If a field contains a value table but is not a foreign key, when that field appears on a screen no validation is done on the field. The value table does not perform validation. Only a foreign key validates values.

With only a value table, a down-arrow will not appear at the end of the field as it does for a foreign key field. However, if you place your cursor in the field and press F4, the F4 key will still display a list of values from the value table. There is no indication on the screen that F4 functionality is available, and no checking is done if a value is entered in the field.

This functionality could be useful when you want to provide the user with a list of suggested values but want to allow other values to be typed into the field as well.

Special Table Fields

Two types of table fields need special treatment:

Currency Fields

Assume you go to the bank and ask for 1000. The teller would probably ask you "1000 what?". You might reply "1000 dollars." Or you might be even more specific and ask for "1000 U.S. dollars." Monetary amounts in R/3 are stored in two fields: one to hold the actual numeric amount (1000) and one to hold the currency (USD). The numeric amount field is called the currency field and the other field is called the currency key field.

TIP
What many people refer to as currency is actually a currency code (USD for U.S. dollar, CAD for Canadian dollar, and so on). SAP uses the word "key" as a synonym for "code," so it calls the field containing the currency code the "currency key." This applies to most code fields in R/3; the country code is called the country key, the region code is the region key, and so on.

Currency fields have these requirements:

The currency key is known as the reference field. It is another field in the same or another table. It must have the type CUKY (currency key) in the domain. It indicates the currency key for the currency field that it references.

For example, assume you create a table containing a list of payments made to vendors. You would need a payment amount field (field name wrbtr) and a currency key field (usually waers) to completely indicate the amount and currency key used for that payment. To link the two fields together, you would double-click on the currency field name in the table and then enter the currency key table and field name in the fields Reference Table and Reference Field (see Figure 4.14).

Figure 4.14: The reference fields on the Display Field screen.

Quantity Fields

A quantity field is a field that contains a numeric measurement. For example, such a field might contain a measure of weight, length, temperature, or electricity. To be meaningful, a unit of measure such as pounds, inches, Fahrenheit, or kilovolts must accompany it. Quantity fields have these requirements:

The UNIT field is the reference field for the QUAN field. The UNIT field can be in the same table or in another table. The UNIT field contains the measurement unit for the measurement in the quantity field.

For example, in table ztxmara, the ntgew field contains the net weight of a material and the gewei field contains the units, such as pounds or kilograms (see Figure 4.15). The ntgew field has a data type of QUAN and the data type of gewei is UNIT. If you double-click on ntgew, you will see that the reference table is ztxmara and the reference field is gewei (see Figure 4.16).

Figure 4.15: This is the structure of the ztxmara table showing the ntgew and gewei fields.

Figure 4.16: These are the field attributes for the ntgew field. Notice that the two fields Reference Table and Reference Field provide the link to the UNIT field.

Structures in the Data Dictionary

In addition to tables, structures can also be defined in the Data Dictionary. As you might expect, a structure is a series of fields grouped together under a common name. It is very similar to a table. Like a table, it can be used within a program on the tables statement to define a work area.

The differences between a structure and a table are as follows:

A structure doesn't have an associated database table.

Structures follow the same naming conventions as transparent tables, and you cannot have a table and structure of the same name.

You would create a structure in the DDIC if you wanted to define the same work area in multiple programs. For example, if you wanted to write records to a sequential file using one ABAP/4 program and then read them in using another, both programs need to know the layout of those records. You would create a structure in the DDIC that defines the record layout, and then name that structure on the tables statement in both programs. This would create an identical work area in both programs.

The procedure for creating a structure is almost the same as for creating a transparent table. (Please refer to the procedures for creating a transparent table.) The only differences are the following:

Understanding Includes

A structure can contain another structure. In other words, you can nest structures one within another. This can be used to reduce maintenance by grouping fields together into a structure and then including that structure within another structure, or even within a table.

For example, a person's address is composed of a set of fields such as street, city, region, country, and postal code. You might need to keep such an address in multiple tables. Within the vendor table you might want to keep the vendor's address, in the customer master the customer's address, and in the employee table the employee's address. If you create a structure to group the fields that form the address together, you could then include that structure into the table definitions. When the table is activated, the fields of the structure are added to and exist in the database table as if they were defined within it. They will have the same name as the name in the structure. To include a structure within a table, (or within another structure), type .INCLUDE in the field name column, and enter the structure name in the data element column.

In Figure 4.17, the table ztxempl (employee table) includes structure ztxaddr (see Figure 4.18), which consists of address fields stras (street), ort01 (city), regio, and land1. Table ztxempl therefore contains these fields: ztxempl-stras, ztxempl-ort01, ztxempl-regio, and ztxempl-land1.

Figure 4.17: The ztxempl table contains an .INCLUDE to cause the fields of ztxaddr to be added to it.

Figure 4.18: The structure ztxaddr contains the address fields that are included in table ztxempl.

To view the expanded includes within the definition of the table itself, choose the menu path Extras->Substructures->Expand All Substr.. All the field names from an included structure will appear below the .INCLUDE, as shown in Figure 4.19.

Figure 4.19: The ztxempl table .INCLU-XXX form of the include statement so that ztxaddr can be included twice without duplicating field names.

The chain of structures created by including one structure within another is called an include chain. The maximum nesting depth is nine, and only one table can be included in an include chain. In other words, you cannot include a table within a table.

Including the Same Structure Multiple Times

You can include the same structure multiple times within a table if, instead of .INCLUDE, you use .INCLU-XXX. The XXX represents your choice of any three characters. These three characters will be appended to each field name that is included to make it unique.

For example, assume you need to store two addresses in ztxempl: a home address and a mailing address. As shown in Figure 4.20, ztxempl2 includes structure ztxaddr twice. The first include, .INCLU-01, causes "01" to be appended to each of the field names of the structure. The second, .INCLU-02, appends "02" to each. The result, shown in Figure 4.21, can be seen by choosing the menu path Extras->Substructures->Expand All Substr.

Figure 4.20: The ztxempl2 table uses the .INCLU-XXX form of the include statement so that ztxaddr can be included twice without duplicating field names.

Figure 4.21: Choosing the menu path Extras-> Substructures->Expand all substr. causes fields from the structures appear in the list.

TIP
Structures and tables in R/3 are so similar that when looking at the Data Dictionary definition of each, they are difficult to tell apart. To determine which one you are looking at, look to the right of the name field. There you will see either Transparent table or Structure.

Summary

Q&A

Q
Can an ABAP/4 program ignore foreign key relationships and insert rows that violate referential integrity?
A
Yes. However, most of the time the data comes to your program via a user interface. The user interface validates the information before you insert it into the database, so the foreign keys have already been checked. Even when importing data from external systems, you should use BDC to update the tables. This also enforces referential integrity by simulating user sessions and passing the data to the program via the interface. However, if you update the database without passing the data through the user interface, your ABAP/4 program must be written carefully so that it doesn't violate referential integrity.
Q
If I want to guarantee the referential integrity of my database, could I go to the DBMS level and create integrity constraints? How would it affect R/3?
A
No, never update the RDBMS definitions directly. You should only use the R/3 DDIC to change the definitions within the database. If you update the database manually, the database definitions and the R/3 DDIC definitions will be inconsistent, and the results will be unpredictable.
Q
If I delete a row from a check table, can I cause rows having the same value to be automatically deleted from the foreign key table also?
A
No. This feature is usually termed "cascade on delete", and it is not available in R/3.
Q
The foreign key type Key Fields Of A Text Table causes the system to behave differently. Do the other foreign key field types have any functionality behind them?
A
No. Only Key Fields Of A Text Table employs system functionality. The others do not cause anything to happen - they are mostly for documentation.
Q
Can structures have foreign key definitions?
A
Yes. This is commonly done because a table that includes a structure can inherit foreign key definitions from it. You can turn off this inheritance when you include the structure if you so desire.

Workshop

The following exercises will give you practice creating foreign keys, special fields, and structures.

Quiz

  1. For a foreign key to be created, what must the foreign key field and the check table field have in common?
  2. What is the syntax for including structure zs1 within table zt1?
  3. What is the purpose of a text table?
  4. Assume that check table zc exists and has a primary key consisting of mandt, f1, and f2. What should the primary key fields of a text table for zc be?
  5. If you create a currency field, what type of field must it reference?

Exercise 1

Create a country code check table named -t005. It will be used to validate all country codes entered into your tables. The specifications appear in Table 4.5.

Table 4.5  Fields and Their Characteristics for Table -T005
Description
  Field Name
PK
DE Name
Client
  mandt
x
mandt
Country Key
  land1
x
-land1

After you have activated this table, go into the -land1 domain and type -t005 in the Value Table field, then activate it. Use SE16 to add country codes to your table.

Exercise 2

Create a country code check table named -t005s. It will be used to validate all region codes entered into your tables. The specifications appear in Table 4.6.

Table 4.6  Fields and Their Characteristics for Table -T005S
Description
  Field Name
PK
DE Name
CheckTable
Client
  mandt
x
mandt
 
Country Key
  land1
x
-land1
-t005
Region Code
  regio
x
-regio
 

After you have activated this table, go into the -regio domain and type -t005s in the Value Table field, then activate it. Use SE16 to add region codes to your table. Test your foreign key on land1 by trying to enter an invalid country code.

Exercise 3

Add appropriate foreign key relationships to the land1 and regio fields in your -lfa1 table. Use SE16 to update the existing rows within -lfa1 and enter valid country and region codes.

Exercise 4

Create a check table for field cityc in the customer master table -kna1 that you created on Day 3. Name this table -t005g. It should contain a list of valid city codes. The specifications for it appear in Table 4.7.

Table 4.7  Fields and Their Characteristics for Table -T005G
Description
  Field Name
PK
DE Name
DM Name
Type
Len
CheckTable
Client
  mandt
x
mandt
 
 
 
 
Country Key
  land1
x
-land1
 
 
 
-t005
Region Code
  regio
x
-regio
 
 
 
-t005s
City Code
  cityc
x
-cityc
-cityc
CHAR
4
 

Don't forget to create the foreign key relationships on the land1 and regio fields.

Exercise 5

After you have activated you table, go into the -cityc domain and put -t005g in the Value Table field, then activate. Use SE16 to add city codes to table -t005g, and test the foreign keys by trying to enter invalid country and region codes. Then create the foreign key within the -kna1 table on the cityc field, and use SE16 to add customers to -kna1. Test the foreign key relationship by attempting to enter invalid city codes. Create a text table for -t005g. Name it -t005h; the specifications are shown in Table 4.8.

Table 4.8  Fields and Their Characteristics for Table -T005H
Description
  Field Name
PK
DE Name
DM Name
Type
Len
CheckTable
 
Client
  mandt
x
mandt
 
 
 
 
 
Language Key
  spras
x
spras
 
 
 
t002
 
Country Key
  land1
x
-land1
 
 
 
 
-t005
Region Code
  regio
x
-regio
 
 
 
 
-t005s
City Code
  cityc
x
-cityc
 
 
 
 
-t005g
Description
  bezei  -bezei20text20 CHAR20  

The foreign key relationship on the cityc field should have the Foreign Key Field Type "Key Fields Of A Text Table". Use SE16 to create descriptions for each city code. Create these descriptions in two languages: E (English) and D (Deutsche). Test your text table by creating a new entry in -kna1 and pressing F4 in the cityc field. The descriptions for each city code should appear in the list, but only in your current logon language. Sign on in German and retest to see the German descriptions.

Exercise 6

Create a structure named -tel. The specifications appear in Table 4.9.

Table 4.9  Fields and Their Characteristics for Structure -TEL
Description
  Field NameDE Name
First Telephone Number
  telf1telf1
Second Telephone Number
  telf2telf2
Fax Number
  telfxtelfx
Telex Number
  telx1telx1

Include this structure at the end of your -kna1 and -lfa1 tables. Use SE16 to update some of your existing records and add telephone numbers to them.

Exercise 7

Add two more fields to the -kna1 table. The specifications are shown in Table 4.10.

Table 4.10  Additional Fields and Their Characteristics for Table -KNA1
Description
  Field NameDE Name DM NameTypeLen DecimalsChkTbl
Credit Limit
  creditl--creditl --creditl CURR122  
Currency Key
  waerswaers      tcurc

The creditl field will contain the customer's credit limit. The waers field will contain the currency key for which the credit limit is valid. Don't forget to add the foreign key relationship to check table tcurc. Create F1 help for the creditl field by pressing the documentation button in the data element.

After adding these two fields, double-click creditl, in the Reference Table field type -kna1, and in the Reference Field type creditl. Then activate your table. Use SE16 to update customer records and add a credit limit to each. View the F1 help you created.