Handling Changes to Database Tables

If a change in the dictionary affects a database table, directly or indirectly, this change needs to be applied to the database table. This is called the database table adjustment.

There are three different approaches for adjusting the database table:

ALTER TABLE

Using this SQL statement, the dictionary tool can change (alter) the table layout on the database. Data in the table gets adjusted if necessary. However, not every change can be implemented using ALTER TABLE. In particular, changes that imply the risk of data losses or conversion errors are not implemented with ALTER TABLE.

Delete and Create

This is the simplest approach. The database object gets deleted and created again based on the changed definition. This is of course only a good idea if the table is empty, or if the data stored in the table is redundant.

Table Conversion

In this procedure, the table is first created on the database under a new name. Then the data is copied from the old table to the new table, converting it if necessary. Finally, the old table is deleted and the new table is renamed to replace the old table.

During a table conversion, a partial loss of data is possible, for example, when you remove a table field or reduce its length. Runtime errors can occur if you change the primary key or the type of an existing table field.

Play the video to learn more about activating changes.

When it is not possible to implement your changes on the database during the activation, the editor displays error message "TABLE <...> was not activated".

To see more details, right-click anywhere on the source code and choose Open With → Dictionary Log .

The Dictionary Log view then tells you that ALTER TABLE is not possible, and displays error message Structure change at field level (convert table <...> ).

You perform the manual conversion using a quick fix. In the editor, right-click on the table name and choose Quick fix. Alternatively, place the cursor on the table name and press Ctrl + 1. There are two quick fixes to adjust and activate a database table:

Adjust and activate database table, convert data

Choose this quick fix if you want to keep and convert your data. In most situations, this will be the right option.

Adjust and activate database table, delete data

Choose this quick fix if the data in the table are not needed anymore.

Avoid using quick fix Delete table on database. This removes the table from the database. After this, any access to the table will fail until you activate the database table in the dictionary again.

Note

Having an activation error is not a prerequisite for using these quick fixes. In principle, you can adjust or delete a table on the database at any time as long as you have sufficient authorizations.

When you choose Adjust an activate database table, convert data, the Quick Assist view will automatically open below the editor window. In the section on the left, you can trigger the same, or another quick fix again. The section on the right allows you to analyze the last adjustment in more detail. Here you find the start time of the last adjustment, and a link to the detailed DB Adjusting Log.

The DB Adjusting Log contains detailed information on how the table was adjusted. It is particularly useful when something goes wrong during the conversion process.

Before we discuss in a bit more detail what exactly happens during a table conversion, watch the following video to see the adjustment of a database table.

Select each step to learn more about the conversion process.

Earlier, we discussed how the database table needs to be adjusted after a direct change of the table definition in the ABAP dictionary. Now we want to have a look at indirect changes.

One example for such an indirect change is a change of the basic type of a domain, which describes a field of the database table. Another example is the replacement of the used domain in a data element. If the table includes a structure, a change of the include structure also affects the table on the database.

When you activate a dictionary object, the tool adjusts all dependent objects as well. However, if a database table is affected, the tool stops the activation with error message Domain <...> cannot be activated due to dependent tables. This error is independent from the question, whether it is possible to adjust the affected database tables directly or whether a manual conversion is required.

In the example, domain ZS4D430_DEPARMENT_ID is used indirectly in table definition ZS4D430_EMPLOYEE. After a length change from 20 to 40, the activation fails with the displayed error message, even though the length extension of a field can be adjusted using ALTER TABLE.

The error message comes with a quick fix. To invoke the quick fix, right-click the error message on the Problems view. Alternatively, left-click the error message an press Ctrl + 1. On the quick fix window that opens, choose Finish.

The outcome of the quick fix is displayed on the Quick Assist view. The activation is successful if it was possible to adjust all dependent tables using either ALTER TABLE or Delete and Create.

The activation fails if a least one of the dependent tables needs manual adjustment. Check the Activation log to identify the affected database tables and adjust them manually as we did before.

Hint

After adjusting all dependent tables, return to the dictionary object that you changed directly, and make sure that it is in status Active. In our example, this is domain ZS4D430_DEPARTMENT_ID.