Database Concepts not practiced correctly in SAP !!!!!!!!!!!!!!!!!!!
This is my first blog in SCN. If I have made any mistake in my following blog pls bear with me and correct me. Thanks in advance for you suggestion.
Basic DB Concept:
Primary key and foreign key relationship is the key to any database application. Using this relationship the whole schema of any application will be designed. As all knows that if a field of a table (child table) is designed as a foreign key which points to another table (parent table), the relationship between the two tables is established.
Now the child table’s foreign key field will only accept the values which are present in parent table. If we try to enter the value which is not present in the parent table then the database application will throw an error. Also the database application will not allow us to delete a record in parent table if some records are available in the child table which points to the corresponding row in the parent table (foreign key relationship).
The system will throw an error message or exception when the insertion/deletion is happened through programmatically or through manually. This is the common mantra for any database application (Oracle, MySQL, SQL Server).
Strange Observations in SAP:
But some of the checks are not done in SAP
In SAP we follow the parent key and foreign key relationship using the concept of CHECK TABLE. We define the primary key in the table and child table refers the parent table as CHECK TABLE.
SAP comes with some default tables which we call as STANDARD TABLE and it allows us to create tables which we term as CUSTOMIZED TABLE. We usually update the STANDARD TABLE through BAPIs or through STANDARD transactions.
We can enter the records to the CUSTOMIZED table using
- Table Maintenance Generator
- Programming lines (Insert statements)
I have created two customized tables ZPARENT and ZCHILD for Demo purpose.
Here ZPARENT is the parent table and Department is the primary key
ZCHILD is the child table & Department, Section_Name is the primary key (Combined primary key)
Records in the Parent Table:
While inserting a record with values MECH MECHANICAL A , the system will not allow to enter since MECH is not present in parent table.
If the same record is entered programmatically, the system allows which it should not.
Also database system should not allow deleting the records in the parent table if records are found in child table for the corresponding records
Records in ZCHILD
Now if we try to delete the record CSE in parent table the system should not allow since 2 records corresponding to CSE are present in ZCHILD.
But the system allows deleting the records which it should not.
Tables are the building blocks for SAP and but this elementary checks are not maintained. This is entirely disgusting if a new user comes and works on SAP. There may be some particular reason why the SAP has left it out. If so please provide me the solution as I am new to SAP and really confused.