Working with Database Tables
Making a Copy of a Table
This article will look at ways in which one can change the transparent tables created earlier. It is important to know how to do this, and the implications of adding and taking away fields for the underlying data in a database table.
Let’s take a look at the ZEMPLOYEES table created in article about the SAP Data Dictionary. In the SAP GUI, key in transaction code SE11 to access the ABAP dictionary, then display the table:
It is important to realise that whenever one wants to change a database table, there is a risk of losing data, especially where key fields in the table are being affected. The database system itself will try to determine whether adjustments can be made by deleting and creating new items which change the underlying database catalogue, or whether what has already defined has to be re-implemented.
Quite often, when working with large tables, one has to manage the manipulation of the data oneself, so as to be sure that data is not lost. Deleting fields is quite a simple task, the table structure and its contents can add certain complications. Before starting any database change tasks, it is important to mitigate against as many risks as possible, and start by using a copy of the database table, allowing one to test out any changes one may want to make, without affecting the initial table and its underlying data.
When you copy a database table, it is only the structure itself which is copied, meaning only its properties – fields and so on, not the actual data.
Step back to the initial SE11 screen. With ZEMPLOYEES in the Database table field, click the Copy button, then give the new table of ZEMPLOYEES2. The ‘Create Object Directory Entry’ box will appear and as before, select ‘Local Object’:
A copy of the table has now been created. Choose display at the SE11 screen and the copy will appear. The table’s status will read as ‘New’. It must be activated, so click the ‘Change’ button (the Pencil icon in the toolbar), and then Activate:
Note that all of the fields in the table, since they have been copied, are already active. This is why it is only the table itself which has to be activated here. If you try to look at the table, you will find there are no contents, because only the structure was copied, not the underlying data. To create records, from the ‘Utilities’ menu, select ‘Table Contents’ and then ‘Create Entries’ to display the screen where the records for the table can be created as before.
Insert some records, click the Contents button, and then view the new table:
Add New Fields
Next, a new field will be added. This will be a non-key field and will be called INITIALS.
Create a new Data element for this named ZINITIALS using forward navigation. For the data element, set the short text to ‘Initials’ and set the domain to CHAR03 (a character string of length 3). In the Field label boxes type ‘Initials’, then activate the Data element. The table should now have a new field like this:
Create another 3 more new fields and configure them as follows:
· Field Name ‘GENDER’
o Set the Data element to ‘ZGENDER’. Configure the data element as follows:
§ Short text: ‘Gender’
§ Domain: ‘CHAR01’
§ Field labels set to ‘Gender’
· SALARY
o Set the Data element to ZSALARY
§ Short text: ‘Salary’
§ Domain: ‘CURR9’ (This has a length of 9, with 2 decimal places)
§ Field labels set to ‘Salary’.
One thing to note about the Salary field is that, because it is a currency, another field for this currency must be created and attached to ZSALARY to indicate what currency the salary is in. If you try to activate the table without doing this, an error message will appear asking for a reference field to specify the currency.
Create a new field called ECURRENCY. Currency fields should already exist in the system, so the Data element here will be a pre-existing one named CURCY. Type this, press enter and the remaining fields should fill in automatically, leaving the new section of the table looking like this:
Next, the system must be told that the Salary field is referencing the Currency field. Above the table will be able to see a tab labelled ‘Currency/Quantity Fields’. Click this and the table will be shown with two boxes to be filled in for the Salary field, since it has already been specified that the domain for this field is Currency. In the ‘Reference table’ column enter the name of the table, ‘ZEMPLOYEES2’ and in the ‘Reference field’ column, the name of the new Currency Key, ‘ECURRENCY’. Now the table can be activated error free.
Foreign Keys
As shown earlier enter a new record. You will see that the currency key does not offer any kind of drop-down menu, here for this example, type GBP, indicating Great British Pounds:
Save the record, and then return to the design of the table, where we can now add some error-checking to ensure that valid entries are made in the Currency key field.
To enable error-checking on the currency key field, we need to make use of a Foreign Key. These are used to ensure that only valid values can be entered into a field. Use forward navigation on the CURCY data element. Look at the Data type tab and you will see that the data element refers to a standard SAP domain, WAERS:
Double-click the WAERS domain to use forward navigation again. Look at the ‘Value range’ tab in this window, a ‘Value table’ box is visible at the bottom, labelled TCURC:
A Value table can be used to determine the entries that can be made in the field based on this domain. Double-click TCURC to again use forward navigation and this value table will be displayed.
Use the data browser to look at the data in this table. If you scroll down, the GBP value from before can be found, among a number of others. This table can be used to ensure that, in future, only entries found in this table can be entered into our new table ZEMPLOYEES2
Return to the ‘Maintain table’ screen for ZEMPLOYEES2, highlight the ECURRENCY field, and click the Foreign key button visible in the toolbar above: 
Choose ‘Yes’ in the box which appears and a ‘Create Foreign Key’ window will emerge. Type the short text ‘Check Currency Field’. A small table is visible, detailing the two key fields from the TCURC table and the ZEMPLOYEES2 table. The option is available to ensure that the foreign key matches both fields, so that when the user is allowed to select an entry, the records returned will only be valid for the Client which is being worked in.
Here though, the Client is not to be chosen as part of the key, so select the Check-box ‘Generic’ for the top row, which refers to the Client, and remove the text from the two boxes on this row where this is possible. Then click the ‘Copy’ button. The foreign key will be created:
Activate the table, and then browse the data. Now, select the currency key and either press the F4 key or select the drop-down box that appears, displaying all valid entries for this field. If you were in record change mode you will then be able to select a value from the table and see it update your zemployees 2 record. Try it out and select USD (US Dollar).
Append Structures
Having looked at foreign keys, the next thing to look at are Append structures. These can be used to add additional fields. This is the preferred method for maintaining SAP delivered tables and quite often for customer-specific tables. If one does not use Append structures, problems can arise if, for example, a new version of SAP is used which does not correspond with aspects of the tables already created, resulting in serious errors.
Append structures give a safe way to enhance tables. When these are used, the initial table remains unchanged, removing any risk of changes being overwritten later if a different version of SAP is used. Quite often, a table may have multiple Append structures applied to it, because different development needs have arisen as time has gone by and people have wanted to add further fields to the standard SAP tables.
In the SE11 Maintain Table screen, go to the ‘Append structure’ button on the right of the top toolbar:
Click this, and the system will suggest a name, ZAZEMPLOYEES2 (note that this, again, must begin with a Z). Accept this and you will be presented with what looks like an empty table structure. Enter the Short text “Extra Fields For Employees”, and then move down to the table.
Note that the first field now is called ‘Component’. This is where new fields are created. However, it may be useful to differentiate between fields created in the main table, and the new components created here in the Append structure. Since both must comply with the customer name rules, where Z was used in the main table, here use ZZ.
For the first component, a ‘Department’ field will be created. Type in the ‘Component’ box ‘ZZDEPT’ and the same again in ‘Component type’. For this Component type, use forward navigation in the same way that it was used for the Data element before, double-clicking to create. Save the Append structure as a local object when prompted, and then select to create a Data element when prompted subsequently.
The familiar data element screen will now appear. Type ‘Department’ for the short text, use CHAR10 for the domain and ‘Department’ again for the Field labels, then activate the data element. Step back to the Append structure screen, then Activate:
Return to the main table screen, where a new row displaying the Append structure will have been created. To then access this structure, simply double-click the row. In Change mode only the ‘.APPEND’ line will be visible by default, but in Display mode the fields created within this will appear below:
This is a very useful way to add new fields to a table without affecting the structure of the table itself. If one then browses the data as normal, a new column will have been called ‘Department’. Data can then be entered into this field just like it can for any other:
Include Structures
Include structures are similar to Append structures, with the main difference being that they are re-usable objects and can be linked to many other tables, ABAP programs, dialogue programs and structures. It is important to keep in mind that Include structures must be flat structures, meaning that they cannot hold any additional structure within them, and that the maximum length of the fields within an include structure is 16 characters.
There is no Include structure button in the way that there is an Append structure button. To create one, first ensure Change mode is selected. Where the cursor is placed is important here, as wherever the cursor is when the Include structure is created, it will be created one row above. If you want the Include structure to be part of the table key, it must appear at the top, because all table fields used as a table key need to be grouped together at the top. In this instance though, it will just be inserted above the Append structure. Place the cursor on the ‘.APPEND’ row, select the ‘Edit’ menu, then ‘Include’ and ‘Insert’.
In the window that appears, enter ‘ZEMPL’ in the ‘Structure’ field and click the continue button. A warning box will appear stating that this is not yet active, dismiss this, and the Include structure should now appear in the table:
To add a field to this, use forward navigation as before, double-clicking where ‘.INCLUDE ZEMPL’ appears, save and choose ‘Yes’ to create the structure. The screen which then appears is very similar to the Append structure screen.
Type the Short text “Employee Include” and begin to create a field (the boxes are, like in the Append structure, labelled ‘Component’), this time for location, called ZZLOCAT, and use ZLOCAT for the ‘Component type’. Use forward navigation again to create this Data element with Short text ‘Location’, the domain CHAR10 and ‘Location’ again for the Field labels, then Activate this as usual. Activate the Include structure once the field has been created and return to the main table to see the Include structure located just where we wanted it, above the Append structure:
Activate the table now, and view the contents. The Location column should now be visible, and these records can now be edited and created like any other:
If one switches to Display mode, the field created in the Include structure can be seen in the context of the main table, albeit in a different colour:
In Change mode, these fields can be seen by selecting the ‘.INCLUDE’ row and clicking the ‘Expand include’ icon (the same works for the Append structure also):
Key Fields
If you want to add or remove fields which are designated key fields, then it is important to take into consideration what will be going on in the database itself. All of the new elements which have been created for this table have their features applied by the system to the ABAP dictionary, not the underlying database. When any key field is adjusted, the system has to apply changes to the underlying database itself. If there is data in the table, and key fields are changed, this can have unintended consequences.
If you introduce a new key field, this will probably not have a large effect. However, if one makes a key field no longer a key field, this will require consideration, because if there is a lot of data in the underlying database, by taking away a key field, duplicate records could be introduced. Corrupt data or records being deleted from the table can also happen here.
Let’s see how we can add, remove and alter fields without these hazards.
Open the full ZEMPLOYEES2 table in the ABAP Dictionary ‘Maintain Table’ screen. Let’s change the ‘Surname’ field by turning it into a key field.
Check the two boxes (key and Index) by ‘SURNAME’ and Activate the table. When you now view the table contents, the surname column will be a darker colour, indicating that it is now a key field. Beyond this though, it appears very little has changed:
Now, uncheck the boxes on the ‘Maintain Table’ screen, to make it no longer a key field. When you try to activate the table an error message appears, refusing to activate the table as data may be lost with the removal of a key field:
To activate the table against what seem to be the wishes of the system (after all, one knows the data will be fine as the surname field has not been operating as a key field at any point previously), a different transaction must be used.
From the ‘Utilities’ menu, select ‘Database utility’, or use transaction code SE14. A new screen will appear:
This transaction lets us automatically adjust the data held in our table when making adjustments to the database table structure. Environments where tables are being worked on may contain a huge number of records. With this in mind, this transaction can be executed as a background process. However, for our example the ‘Direct’ option is the option to choose because we know we have very few records in our database table. Select this, and then click ‘Activate and adjust database’ with ‘Save data’ radio button selected. Say ‘Yes’ when the box asks “Request: ‘Adjust’” and notice the status bar should indicate the success of this execution. Then, step back to the ‘Maintain Table’ screen and you will see the table should be Active with the surname field no longer key.
To insert a new field as part of the table key, you must be able to adjust the location of fields on the screen. For example, if you wanted to create a new field above the surname field, you would highlight the row and then click the ‘Insert row’ icon in the toolbar. This toolbar also includes ‘Cut’, ‘Copy’ and ‘Paste’ options, allowing for rows to be moved up and down if there is a need to do this:
Deleting Fields
While infrequent, occasionally there may be a need to remove a field from a table. When doing this, it is important to take special care, as data can be lost in the process. Certainly in the case of key fields.
If, for example, the Currency key field was removed from our table, the foreign key relationship to the TCURC table would be removed. As the SALARY field has to have a related Currency Key this would cause the table to no longer continue working, and likely make the ZEMPLOYEES2 table become inactive.
When deleting fields it is important to ask oneself whether the data being held in the table is being used elsewhere, and whether its deletion will have further consequences. If you do try to delete fields which are being used elsewhere, the SAP system should try to prevent this, or at least issue a stern warning. This is not necessarily to be relied upon though, so always ensure to check manually what the effects of deletion are likely to be. Also, if you do delete fields, the table will have to be adjusted via the SE14 transaction to be activated again.
Create a new field, above ‘.INCLUDE’, named ‘ZAWESOME’. Use a previously created Data element, here ZTITLE just to save time, and activate the table:
Create a new record in the table. The data here is not important and will be deleted, so the content can be anything:
Now, to delete the field, highlight it in the ‘Maintain Table’ screen, and click the ‘Remove row’ icon, in the toolbar next to ‘Insert row’. The row will disappear, but when you try to activate the table, an error message will appear:
Transaction SE14 must again be used to adjust the table so the change can be applied. Follow the same steps as in the previous section to perform this task. Once this is complete, view the table again. The column has disappeared, and the data which was contained within it lost:
To see what happens when a key field is deleted, return to the ABAP Dictionary initial screen and make a copy of ZEMPLOYEES2, called, unsurprisingly, ZEMPLOYEES3. Doing this will allow the ZEMPLOYEES2 table to not be damaged in this risky procedure. Activate the new table (which, don’t forget, will be empty of records). As before, again make the Surname field a key field. Now create some records for this table:
To save time creating new records, the same data was replicated here, with only slight changes to the key fields. Remember that it is only one key field per entry which must be unique for that particularly record to be unique itself.
Now, the surname field will be deleted, and the effects of deleting this key field observed. By removing this key field, the only unique data which will be held for each record will be the Employee Number and Client. Since SMITH and SMITH2, and ANDREWS and ANDREWS-2 have the same Employee Number and Client, these will no longer hold unique key field data, leaving duplicate records, which the system will not allow.
Remove the Surname field; try to activate the table, and error messages will appear. Go through SE14 to adjust the table for activation. When you now view the table, the Surname field is gone, and two records have been lost, leaving only one of the two records for each of the two Employee Numbers used:
Deleting Tables
One will not often have to delete an entire database table, for largely the same reasons as were outlined above for fields. If this does have to be done it is important to remember that one’s own customer-specific tables are the only ones which can be deleted, SAP delivered tables cannot be deleted. Because ZEMPLOYEES3 has only just been created, and nothing else depends on this table, it can be deleted without consequences.
To check whether a table can be deleted without causing unintended consequences elsewhere in the system, return to the ABAP Dictionary’s initial screen. Because the original ZEMPLOYEES table was used in the programs which have been created, use this as a test.
Insert this into the Database table field on the screen and then click the ‘Where-used list’ icon from the toolbar.
Once this is clicked, a dialogue box will appear offering a list of check-boxes. This will then search all of the different areas of the SAP system selected for references to the table ZEMPLOYEES. To execute this search click the Continue icon. Choose ‘Yes’ to the pop-up box, and wait while the system compiles the search results, which here show that this table is being used currently by 2 programs:
Having done this, one now knows that if the ZEMPLOYEES table were to be deleted, these programs would become inactive. By double-clicking these entries, one can see the code in the program where ZEMPLOYEES is referred to, and if you double-click on any line of the program, it will open the program at that line of code in the ABAP Editor. The Where-used button is a very useful tool, which can be invaluable not just when deleting programs, but in many other scenarios.
If you were to try to delete ZEMPLOYEES, the system would not allow this course of action and would prevent it from happening until all the programs that are dependent upon it were either edited to remove references or deleted altogether themselves.
Since nothing depends upon ZEMPLOYEES3, this can be deleted. With the correct name in the ‘Database table’ field, click the ‘Delete’ button in the toolbar:
A box appears stating that the data contained in the table would also be deleted. If you click the green tick icon this time, the system would return to the main screen with the table still intact. If the middle button, illustrated with the trashcan icon is clicked, this will proceed with the deletion. Once this is done, the status bar should confirm the action. If you try to display the table now, it does not exist. Once the deletion is completed, it cannot be undone:
