ABAP Dictionary Database Table

Transparent Tables

A table consists of columns(fields) and can store data in either row or column format known as table entries.

Tables are defined in the ABAP Dictionary independently of the database. A table having the same structure is then created from this table definition in the underlying database.

A table has one or more key fields. Every table must have at least one field marked as key field. The key fields collectively are called as primary key which uniquely identify a table entry. A table definition in the ABAP Dictionary contains the following components.

• Table fields define the field names and data types of the fields contained in the table
• Foreign Keys define the relationship between tables
• Technical Settings control how the table should be created in the database.

View, Examine an SAP Delivered Table

We will examine an SAP delivered table: TCURT. It stores the currency names in different languages.

Execute TCode SE11, choose radio button Database table, enter name – TCURT and click Display.

Attributes Tab

Displays Last Changed, Package, and Original Language of the table.

Delivery and Maintenance Tab

It specifies Delivery Class and Data Browser/Table View Editing.

Delivery Class

This controls the transport of table data during installation, upgrade, client copy, and transportation between customer systems. It is also used in the extended table maintenance. Choose Delivery Class A if the table is to hold master or transaction data.

Data Browser/Table View Editing

Following options are available.

a. Display/Maintenance Allowed with Restrictions: With this option, new entries/rows cannot be created in the table, existing entries cannot be edited or deleted through the table maintenance dialog or maintenance view.
b. Display/Maintenance Allowed: With this option, new rows can be created through the table maintenance dialog or maintenance view. Existing
entries can be edited, viewed, and deleted through the table maintenance dialog or maintenance view.
c. Display/Maintenance Not Allowed: With this option, the table maintenance dialog is not generated when the table is activated. New entries cannot be created, and existing entries cannot be viewed.

Fields Tab

In Fields tab, maintain the fields or columns of the table. Field names start with an alphabet, can be alphanumeric. Maximum length of field name can be 16 characters.

Key check box – at least on field has to be marked as key. Multiple fields can be marked as Key. The Key fields together is considered as Primary Key.

Data Element column – The field is defined referencing a data element. It borrows the technical attributes and the semantics from the data element and the data type, length and short description columns are filled automatically. A field can also be defined using the DDIC data type. Click Predefined Type button and enter the data type, length and short description manually. This approach of defining field is not recommended.

Setting Up Foreign Key Relationship

For all the table field/s that are foreign keys (these fields are primary key fields in the check tables), the foreign key relationships are established. The foreign key relationships of a table field can be established or viewed by positioning the cursor on that field and clicking on the button. Position the cursor on MANDT field and choose Foreign Keys button.

When the Check required check box is enabled, values entered in this field in the secondary table will be checked for existence in the check table. Values not existing in the check table will trigger an error condition.

The Cardinality entries operate as follows:
The cardinality (n:m) describes the foreign key relationship as regards the number of possible dependent rows involved (rows of the foreign key table) vis-à-vis referred rows (rows of the check table).

1. For the left side of the cardinality signifying the check table:
   a. If n = 1 then, for each dependent row there is exactly one row in the check table.
   b. If n = C then, there can be rows in the foreign key table that do not reference any row of the check table (orphan dependent rows can exist).
2. For the right side of the cardinality signifying the foreign key table:
   a. If m = 1, for each row in the check table there is exactly one dependent row.
   b. If m = C then, for each row in the check table there is, at most, one dependent row.
   c. If m = N then, for each row of the check table there is at least one dependent row.
   d. If m = CN then, for each row of the check table there are any number of dependent rows.

Entry help/check Tab

The columns of this tab are non editable and maintained by system.

1. Foreign Keys (check box): This check box, when in enabled state, indicates the field to be a foreign key or part of foreign key (fields can be declared as foreign key or part of foreign key).
2. Check table: If the field is a foreign key or part of foreign key, then the check or primary table name is automatically entered by the system in this column (i.e., Check table).
3. Origin of the input help: This refers to the value help list for this field when this field is appearing on the screen.
4. Srch Help: The name of the search help if assigned appears here.
5. Defaults: Check box that indicates whether the set of values of a domain is restricted through entry of fixed values.
6. Domain: The name of the domain derived from the data element assigned to the field.

Currency/Quantity Fields tab

In this tab we specify a reference table for fields containing a currency (data type CURR) or quantity (data type QUAN). A reference table must contain a field (reference field) with the format for currency keys (data type CUKY) or the format for units (data type UNIT).

Tables store business data. Data Elements gives the semantic information. Domain is used for the technical definition of a table field. If two fields of the table have the same domain, they have the same technical attributes. The table fields have different semantic meaning as they use different data elements.

Technical Settings for Transparent Tables

Click the Technical Settings button on the application toolbar to view the technical settings of the table.

Data class – it is to select table space or database space. It defines the physical area of data base in which the table is logically stored.

Size Category – determines the initial probable space requirement for a table in the database as well as size of next extent.

Buffering – faster access to the database server using the main memory of the application server. An ideal table for buffering would be small sized (a few KB), very frequently accessed, and rarely updated.

Log data changes – all the data changes for this table are logged.

Write access only with JAVA check box: If enabled, contents of the table can be changed from within Java only.

Maintain as transparent table check box: This flag indicates that a table should be transparent and that this attribute should be kept even after a change of release or an upgrade.

Database table buffering

There are three options under Buffering:

(1) Buffering not allowed, specifying no buffering for the table.

(2) Buffering allowed but switched off; the buffering is switched off because the particular database installed does not deliver performance with buffering.

(3) Buffering switched on; the buffering is on because the particular database installed delivers performance with buffering.

Buffering type area: There are three options in buffering type: Single record buff, Generic Area Buffered, and Fully Buffered.

With Single record buff. option, when a row is fetched from a database table, it is retained in RAM (buffered area). If the immediate next fetching of a row from the same table refers to the same row fetched earlier, then no disk operation takes place, and data is transferred from the buffered area of RAM.

Generic Area Buffered: Partial contents of the table are loaded into the buffer. When this buffering option is opted for, you need to enter a number in the field No. of key fields. This number can have a minimum value of 1 and a maximum value equal to the number of fields constituting the primary key (in the present context, 3). If this number is 1, it means the first primary key field; if the number is 2, it means the first, second primary key fields; and so on. The number indicates to the system, for one unique value of how many primary key fields, the buffering data should be loaded.

With the Fully Buffered option, all the rows of the table (full table) are fetched into the buffered area. All subsequent retrieval of row/s will be from the buffered area.

Relationship between domain, data element, and table

Table modification is include structures in table as required. You can re-use structures by including them in other structures or tables. A table cannot be included in another table. Includes may contain other includes.