Creating Database Tables

When you create a database table definition, the editor starts from a template which illustrates the basic layout of the source code. It consists of the following three parts:

Semantic Table Attributes

The code lines starting with the @ sign, are called annotations, and they define semantic table attributes. The semantic attributes are preset to the most common values. We will discuss them a later in this course.

Define Table Statement

The define table statement is followed by the name of the database table. You specify this name when you create the development object. Changing it in the source code leads to a syntax error.

Field List

The pair of brackets after the table name contains the field list of the table. Each field definition ends with a semi-colon ( ; ), and consist of a field name and a field type separated by a colon ( : ). The template for database table definitions suggests a first table field with name client and type abap.clnt. Keep this field to make sure your table remains client-dependent.

When you edit the field list of a database table, it is recommended to use data elements. If the data element already exists, you can start typing its name after the colon sign, then press Strg + Enter to see a list of matching data elements. Scroll up and down the list to display details of the data elements, for example the base type, the description, and field label texts.

If there is no data element that you want to use, you can directly create one using a quick fix. When you enter a type name that does not correspond to a data element, an error message is displayed. This error message comes with a quick fix. Click on the error icon on the left of this code line, or place the cursor on the type name and press Ctrl + 1 to invoke the quick fix.

Note

The error message reads: Field <field name>: Component type or domain used not active or does not exist. Don't let this confuse you. It is not possible to use domains directly in the field list of database tables.

Every database table needs a primary key. The primary key is a unique identification for a table row. It consists of a subset of table fields called key fields. The primary index is created for the primary key of the table. This index provides optimized key access to the table rows when values are specified for the key fields.

In the database table definition, you flag a field as key field by specifying addition key in front of a table field name. At least one table field must be flagged as key field, and there can be a maximum of 16 key fields in each table. The key fields of a table must be located together at the beginning of the table, which means that no non-key fields are allowed between two key fields.

A null value is a special value returned by a database in order to indicate an undefined value or result. Null values do not correspond to any content of data objects in ABAP. If the database returns a null value, it is replaced with the type-specific initial value in ABAP.

Originally, the NOT NULL addition was used to enforce the type-specific initial value on the database to avoid confusion between initial values and null values. By adding NOT NULL to a table field, the developer made sure the table column could not contain null values. This was particularly important for primary keys. Therefore, the NOT NULL addition became mandatory for key fields.

Today, the NOT NULL addition is still mandatory for key fields but it has no effect anymore. The dictionary tool enforces the type-specific initial value for almost all fields, whether you add NOT NULL or not.

The only exception are fields of certain types, namely those for which no initial value exists. For such fields, it is technically not possible to enforce initial values. As a consequence, the editor does not allow you to add NOT NULL to those fields. In the example, data element /DMO/email_address is based on predefined type STRG. The type STRG is one of the predefined types for which no initial value exists. When you add NOT NULL to field email_address, the editor issues a syntax error.

Note

You do not have to manually add NOT NULL to key fields. If during activation the NOT NULL flag is missing for a key field, the editor adds it automatically.

Amount fields are fields based on predefined type CURR. Amount fields in database table definitions require a reference to a currency code field. Currency code fields are based on predefined type CUKY. In the example, the field PRICE is an amount field, and the field CURRENCY_CODE is a currency code field. the reference is defined with annotation @Semantics.amount.currencyCode before the amount field. The argument of the annotation contains the name of the table plus the name of the currency code field separated by a period sign (.) .

Note

Usually, the amount and currency code field are located in the same database table, like in our example. However, it is possible to reference a currency code field in another database table, too.

Similarly, quantity fields, based on predefined type QUAN, require a reference to a unit field, based on predefined type UNIT. The corresponding annotation for quantity fields is @Semantics.quantity.unitOfMeasure .

Note

Classical UI frameworks were built directly on top of the dictionary objects. They evaluated the referenced fields to adjust the display of amounts and quantities based on the currency code or unit. We will see later that for modern UIs that are build on top of CDS views, similar annotations are available for amount and quantity fields in CDS views.

Play the video to learn about quick fixes for Amount and Quantity fields.

The field list is a very important part of a database table definition. But you have to provide more information to define a database table. You define the table properties with the following techniques:

Annotations

Annotations are code lines starting with the @ sign. The annotations are part of the table definition itself, and define technical and semantic properties.

Technical Table Settings

The technical table settings constitute a standalone development object, and can be activated and transported separately from the table. They define properties of the physical database table and influence the access via ABAP SQL.

Note

When you create a new database table, the editor sets the table properties to the most common values. This allows you to activate the table without editing the table properties. But you should always make sure that the default values are appropriate for your table, and change them if necessary.

Play the video to learn about table annotations.

Some table properties are not maintained using annotations, but with a dedicated form-based editor. To open this Technical Table Settings editor, right-click anywhere in the database table definition and choose Open Others → Technical Settings.

Hint

The technical table settings are also displayed in the Project Explorer, as a sub-element of the database table.

The Technical Table Settings dialog consist of the following three sections:

General Settings

The main purpose of the general settings is to influence the way the table is created on the database. Whether a given setting is passed to the database or not, depends on the database system. The Data Class for example, no longer has an effect with SAP HANA as a database system. However, it is always mandatory to provide a value.

Buffering

With the buffering settings, you define how, and whether buffering a dictionary database table is allowed in the table buffer of an Application Server instance. When tables with active buffering are accessed using ABAP SQL, the table buffer is used implicitly.

Data Changes

If logging is activated in this section, any changes to table entries done in ABAP SQL are logged in the log table DBTABLOG. There are various prerequisites and restrictions. Details can be found in the ABAP documentation.

This logging mechanism should be restricted to very important database tables that are changed only rarely. Because the data changes of all tables with this flag are logged in the same database table, lock situations can occur for the log table. In general, logging should be done by the application logic.

The general settings can be used to influence the way the table is created on the database. But not all settings have an influence. It depends on the database system whether a given technical setting is actually passed to the database or not.

The following general settings exist:

Data Class

The data class only has an effect for Oracle databases. It is ignored by all other databases - especially by SAP HANA. It is still mandatory, but only as a means of documentation. The most important data classes for application data are:

• APPL0 (master data) - data that is rarely changed. One example of master data is data in a table of addresses, such as names, postal addresses, and telephone numbers.
• APPL1 (transaction data) - data that is frequently changed. One example of transaction data is warehouse stock that changes each time an order is placed.
• APPL2 (organizational data) - customizing data specified when the system is configured and then changed only rarely. One example is the table T005 that contains the country codes.

There are further data classes for system tables used internally by runtime frameworks, such as DDIC for ABAP Dictionary tables.

Size Category

The size category affects the size of the initial memory area reserved for the table on the database. Values between 0 and 9 can be specified, and intervals of expected rows are assigned to these values. The interval limits assigned to each size category depend on the structure of the database table. If the initial space reserved is exceeded, a new memory area is added implicitly in accordance with the chosen size category.

Hint

An appropriate size category should be chosen to prevent too many small memory areas or a memory area that is too big.

Storage Type

The storage type has an effect only if the current database is an SAP HANA database. The following settings can be defined for the SAP HANA database:

• Column store - this setting should be used in the following cases:

  • The DDIC database table is designed for application data analyzed in SAP HANA.
  • The DDIC database table contains a very large number of rows since the storage type Column Store provides better compression.
  • The DDIC database table contains table fields with SAP HANA data types that are supported only by the storage type column store.
• Row store - This setting should only be used in SAP HANA databases if the database table is used mainly for frequent single row accesses.

Hint

For more information about the differences between row store and column store on the SAP HANA database, see the SAP HANA documentation.

Load Unit

The load unit has an effect, only if the current database is an SAP HANA database. The load unit of a table specifies how the data of the table is loaded into the main memory of the SAP HANA database. The SAP HANA database offers the following settings:

• Column Loadable – The complete table is considered as in-memory. The table data is loaded upon system start in a column-oriented format into the main memory of the SAP HANA database. This is the default behavior.
• Page Loadable – By using a feature of the SAP HANA database called Native Storage Extension (NSE), table data is stored on disk in pages rather than in columns. These pages are loaded only on request into a special buffer in the SAP HANA main memory. Unused data in the buffer can be paged out.

Typically, Page Loadable is used for large tables with less frequent accesses.

In the dictionary, you can either enforce a certain load unit setting or just set it as preferred - allowing for a different setting on the database.

Hint

For more information on the Native Storage Extension on the SAP HANA database, see the SAP HANA documentation.

Include structures is a technique where you can add an entire subset of fields to the field list of a database table. You first define a development object of type Structure with the fields that you want to add to the database table. This development object does not define a database object itself. But when you reference it in the field list using statement INCLUDE, the table on the database will have the fields that are defined in the structure at the position of the INCLUDE statement.

In the example, the structure defines three fields. The table definition defines four fields with an include statement between the second and third field. The table on the database then has seven columns altogether.

A common use case for this technique is a data model, where you want to make sure that several database tables contain the same subset of fields with identical types.

In the next examples, we will use an include structure to add administrative fields for users and timestamps to a database table.

Note

Such administrative fields play an important role in the ABAP RESTful application programming model.

This is the definition of a structure with five fields, two for user names, and three for different timestamps.

As you can see, the structure definition is very similar to a database table definition. The main difference is the keyword define structure instead of define table, and the missing of many annotations that were mandatory for a database table.

The five fields of the structure are added to the field list of the database table with keyword INCLUDE, followed by the name of the structure.

Hint

Use the auto-completion functionality of the editor (Ctrl + Space) to choose from the existing structures.

Note that the fields are not copied. The database table definition references the fields of the structure. This means that when you later edit the field list of the structure, the field list of the database table is updated immediately.

A convenient way of checking the result is the tooltip display for the database table. Place the cursor on the name of the database table and press the F2 key. As you can see, the field list of the database table contains the five fields of the include structure.