Confusion around Initial Values marker in SAP Table Definition

  • Article

Lately we had some interesting discussions between our SAP Basis Team and some of our SAP developers. Reason was that there were several different opinions around what the column ‘Initial Values’ in the ‘Fields’ tap in SE11 really means (see below)

clip_image002

Therefore let’s dig a bit deeper and explore how that column impacts how the structure looks on the database. The scenarios and behaviors we are about to display and discuss are generic in the SAP Data Dictionary and NOT dependent on anyone of the different underlying DBMS systems

In order to do so, let’s distinguish several different cases:

Scenario 1: Creating a new table through SE11

When creating a new table through SE11 and defining columns with ‘Initial Values’ check or unchecked like here:

clip_image004

The resulting database object will always have ALL columns set to NOT NULLABLE and default values assigned to it.

Hence it really doesn’t matter whether the ‘Initial_Values’ is checked or not.

 

Scenario 2: Adding columns to an empty table

So we got our table defined and now realize we missed some columns. The table does not have any rows in yet, so we simply go into SE11 and add another two columns like these:

clip_image006

As we did not check the database object that got created during activation, we will end up with two added columns again which are NOT NULLABLE. Means again the SAP logic in the Data Dictionary simply ignored the settings in SE11 ‘hints’ for ‘Initial Values’ and decided on its own to create non-nullable columns and assigned a default value to it.

Sceanrio 3: Appending Columns to a table with data in it

In this scenario, we got data in the table and now want to add columns to the table with data in it. We again would try to add two columns of which we flagged one in ‘Initial Values’ and the other one we didn’t. Like displayed here:

clip_image008

As we activate the table and check the table as it exists on the database, we see a different behavior compared to our scenario #2. The column ADDEDWITHDATA1 where we flagged the ‘Initial Values’ field is becoming a column which on the database will be NOT NULLABLE and a default assigned to it. As such this is not a different behavior as before. However the column ADDEDWITHDATA2 where we didn’t flag the ‘Initial Values’ now became a nullable column on the database without any default values assigned.

In order to push this different behavior, it doesn’t take a lot of data in the table. Actually it only needs one single row in the table.

Scenario 4: Adding a new Key column

There also might be situations where a new key column needs to be added to a table with data. Even not checking the ‘Initial Values’ like shown in this screenshot”

clip_image010

Checking the table as it got changed on the database, two things are noteworthy:

  • The order of the columns as in the screenshot above is not reflected in the database object. The new key column which in SE11 had to be inserted in the third line since all key columns need to be in one block is not in the third position on the database. It actually got appended and is the last column of the database object.
  • The column got created as NON-NULLABLE column with a default value assigned.

Conclusions:

  • Creating a new table or adding columns to empty tables, the column ‘Initial Values’ doesn’t play a role
  • When initially creating a table via SE11, it is not possible to create any columns in the table as being nullable.
  • When you want to create a column which is nullable, you need to create the initial table w/o the column in question. As a second step you insert one row into the table and then you add the column which should be nullable without checking the field ‘Initial Values’
  • The ordering of the columns is affected by the primary key display needs of the UI and does not express chronological ordering or consistency with the underlying database.