Database Concepts not practiced correctly in SAP !!!!!!!!!!!!!!!!!!!
Hi Experts,
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.
Yes , Since SAP also uses Oracle Database and therefore it shouldn't allow to Delete those records which is already present in Check Table.
SAP ABAP applications support not only Oracle databases, but other databases as well. The issue identified here is incorrectly classified as a "database" issue. A database is where data is stored. Database management systems can be used to enforce referential integrity, but so can application code. The choice is up to the application designer. Since SAP applications have from the beginning been written to support multiple database platforms, many functions that a custom application would rely on the DBMS to support, are in fact supported in ABAP code. And this is a perfectly valid model.
So the question becomes rather why is this allowed in ABAP? I have my suspicions as to the best answer to that question but, as I am not an ABAP expert, I will let someone more qualified reply.
Cheers,
David.
I don't know the reason but this must have occurred to so many of us..
It's not just SAP. A lack of referential integrity checks is common in ERPs. I've always assumed there could be a few reasons: when the system was initially built, it was too complex to support referential integrity mechanisms on all supported RDBMSs cleanly, particularly while allowing for in-place modification of tables containing data; or that strict integrity checks would lead to a perceived lack of flexibility; or precisely because the notion of a check table (for the purpose of input help) was conflated with an actual referential integrity relationship; or simply that the designers came to the project with a pre-relational mindset, and decided to use the technology in a way that went against convention. SAP has often done things analogous to the latter when adopting a new technology (sadly most often to their detriment). It's plain to see why it would now be extremely difficult to change the approach. In practice, though, the lack of strict integrity in SAP's underlying database is rarely disastrous.
Hi guys ,
Even i found the same problems ...as a beginner in abap its very confusing.
Here is my summary of this:
What it boils down to is learning, understanding and following good coding practices. It is not the responsibility of the application designer to do everything for you.
I will give you an analogy. In higher-level programming languages like Visual Basic, many things are abstracted from you, such as much of the memory management. Essentially, the language is protecting you from doing many things wrong, and as a result makes it easier to code in.
In C++, you must handle more of these tasks on your own, but you also have more flexibility and power when doing so. Is it the fault of the designers of C++ if you don't follow good coding standards, and your program overwrites areas of memory that are in use? No, it is the programmer's fault for not understanding how to properly write applications in that language.
In a nutshell, the more the programming language does for you, the more flexibility you lose when you want to do things that may be more complex.
This also applies to the situation raised here. Just because you "can" do something in ABAP, does not mean that you *should*. Learn and follow good ABAP coding practices, and you'll have the power and flexibility to do just about anything you want to do.
Cheers,
David.
Hi David.
I don't know about this. Thanks a lot for sharing the information.