While we create tables in some cases we require validating the values of some fields with other table field values or a set of valid values. In this case we use foreign keys. The fields for validate mark as foreign keys in the table. The table containing the foreign keys is called the foreign key table and the table containing the valid set of values is called value table. It’s mainly used for the data integrity and consistency in database. Value table is created at domain level. We can specify the set of valid values in domain and can assign this domain with the foreign key field. In this case the valid value in the specified domain is stored in a value table and the foreign key of the table will check against this values.Instaed of value tables we mainly use check tables to check the field against the values in other tables.
For examples there is two tables TI and T2. One of the field values of table T1 should check against the values of the corresponding field of table T2. In this case we mark the field of T1 as foreign key. Now the table T1 is called a foreign key table and table T2 is called as a check table.
Following are the requirements (or qualifications) for assigning a foreign key relationship.
- The fields in check table that is used for foreign key relationship should be primary keys
- Both the foreign key field and check table field should have the same domain name
Step by steps for creating a foreign key in a table
Step 1: Enter the transaction code SE11 (or use menu path Tools->ABAP/4 Workbench, Development->ABAP/4 Dictionary)
Step 2: In the object name field enter the table name where we want to add the foreign key
Step 3: Press the change button.
Step 4: Point your mouse in the field which we want to make as foreign key
Step 5: Enter the foreign keys button
Step 6: Enter the description of foreign key
Step 7: In check table filed enter the name of the check table
Step 8: Now a pop-up will appear with a message “Check table name was created or changed. Generate a proposal for field assignment”. Enter the Yes button
Step 9: Now field names of both tables will be displayed. Press Copy button
Step 10: Check the status bar. A message “Foreign key was transferred” will be there.
Step 11: Enter the Activate button. (You have assigned a foreign key relationship with tables)
In the step 8 instead of automatic assignment we can also manually specify the fields
Compound Foreign Keys
.A compound foreign key made up with more than one fields. It is used when we want to validate the combination of more than one field values against more than one field values of check tables.
Generic foreign keys: When a primary key field is assigned as generic, it doesn’t have a counterpart in the foreign key table, and it won’t participate in the check.
Constant foreign keys: Same as compound foreign keys. But one of the check table fields will be replaced with a constant value. Used for checking against a constant value.
Adapted foreign keys: If all the fields of a compound foreign key are not from same table it is called as an adapted foreign key.
Cardinality of foreign key
Cardinality specifies how many rows are allowed in foreign key table for a corresponding value in check table. It specifies as X: Y (where X point to check table and Y points to foreign key table).
The allowed values for X are
- X=1: if a row in check table deleted then the corresponding rows from foreign key table also deleted.
- X=C: Deletion of check table rows won’t affect the foreign key table rows.
The allowed values for Y are
- Y=1: For each row in check table only one row is allowed in foreign key table
- Y=C: For each row in check table maximum one row is allowed in foreign key table
- Y=N: For each row in check table there is always at least one row in foreign key table.
- Y=CN: There might or might not be rows in the foreign key table for each row in the check table.
Foreign Key field types
- Key fields/candidates: if the key is one of the primary key fields of foreign key table or that key is part of Key candidates that can identity a unique row without using primary key.
- Non-key fields/candidates: (just opposite of the above)
- Key fields of a text table: The text table contains the language description of values in a check table. It used for multi language purposes.
When we create foreign keys we need to specify the appropriate cardinality and key field type for the keys. Look some other types of foreign keys.
Also Read : ABAP Tutorial Home page -> Data Elements and Domains -> ABAP table Technical Settings