topic Re: Data integrity in Application Development and Automation Discussions

Data integrity

hagit — Tue, 29 Jan 2019 11:00:06 GMT

When defining a constraint in a database it affects all the options to enter/modify data. For example if I define a database foreign key, no matter how I try to insert/modify the data the foreign key will be checked. In opposite to it- When I define a foreign key in the application server (SE11) it is possible to enter data that conflicts the foreign key. For example if I update the data via the update clause in ABAP , the foreign key is not checked. The question is how the database constrains can be checked in ABAP (and how can I define it via SAP tools) or Is there another way to define constrains which will affect in all options

Thanks

Hagit

Re: Data integrity

matt — Tue, 29 Jan 2019 15:21:01 GMT

You have to program the constraints yourself. The foreign keys in SE11 applies to screen fields - not database operations as such. Referential integrity is the programmer's responsibility.

Re: Data integrity

hagit — Wed, 30 Jan 2019 06:57:46 GMT

Matthew Billingham,

Thanks for your answer.

So what you are saying is that – In SAP there isn’t any way to define a constraint in one place, which will affect everywhere. When there is more than one program, which input data to the same table, the programmer has to rethink about the constraints

Is it like that?

Thanks

Hagit

Re: Data integrity

matt — Wed, 30 Jan 2019 11:04:00 GMT

Yes. But with good application design, you shouldn't have multiple places of update. For example, have a single class updating the database tables, and handling referential integrity. Multiple programs can call the class without there being any fear of ruining your db integrity.

Re: Data integrity

Sandra_Rossi — Wed, 30 Jan 2019 12:43:48 GMT

Please use the comment button, don't post text using "answer" which is reserved to solutions

Re: Data integrity

hagit — Sun, 03 Feb 2019 08:07:18 GMT

Matthew Billingham,

Thanks for your answer.

What do you mean “have a single class”? Is it different from a single function module?

Could you please give an example for writing the class and implementing it?

According to your suggestion the programmer does not have to rethink about the constraints but has to remember to use the class. (Or if it is another programmer he has to find the class and use it).

Another problem is that I cannot define a check constraint in the application server, as I can in db (for ex. the value must be grater then 5). Therefor the check must be in the program/class level. It will not be checked if someone uses SM30 to update a table.

Thanks in advance

Hagit

Re: Data integrity

matt — Sun, 03 Feb 2019 08:37:02 GMT

It's called "ABAP Objects". Object oriented programming for ABAP. It's been around since before 2000. Probably worth learning. Maybe you should buy a book. There's plenty.

The point is that you have one place, callable from any number of programs, that do your db update.

You might also like to examine the programming concepts of separation of concerns and layering. The book "The Pragmatic Programmer" is good in this respect.

Re: Data integrity

hagit — Sun, 03 Feb 2019 09:18:22 GMT

According to your suggestion the programmer does not have to rethink about the constraints but has to remember to use the class. (Or if it is another programmer he has to find the class and use it).

Another problem is that I cannot define a check constraint in the application server, as I can in db (for ex. the value must be grater the 5). Therefor the check must be in the program/class level. It will not be checked if someone uses SM30 to update a table.

Re: Data integrity

matt — Sun, 03 Feb 2019 10:55:48 GMT

SM30 handles constraints set by foreign keys automatically. Any other constraints you'll have to program using table events. These are very well documented, and there are blogs etc.

Re: Data integrity

hagit — Sun, 03 Feb 2019 15:53:38 GMT

If I understand correctly then table events affect only sm30. If it is true then a programmer has to deal with the constraints twice. Once when creating SM30. Second when update the table via a program

Re: Data integrity

matt — Sun, 03 Feb 2019 17:25:43 GMT

In your program that does some kind of programmatic update of the table you do something like

Call thing that checks constraints

In the event of SM30 you also have something like

Call thing that checks constraints

The thing is where you check the constraints. So, yes, you call it twice, but you only write the constraint checker once. This is basic programming... never write code twice.

Re: Data integrity

hagit — Mon, 04 Feb 2019 15:05:32 GMT

I agree with you “This is basic programming... never write code twice.”

My aim is not just writing the checker code once, but also to call the checker once. I am new is ABAP and SAP. I used to program in ORACLE. There it is possible to define the check as a constraint in the db . This way you write the checker code once and do not have to call the checker at all. No matter how data is inserted/modified, the data will be checked by the constraint. Is there something similar in ABAP?

Re: Data integrity

matt — Mon, 04 Feb 2019 15:08:05 GMT

"Is there something similar in ABAP?"

No. As I said in my very first response "You have to program the constraints yourself".

Re: Data integrity

DoanManhQuynh — Tue, 05 Feb 2019 02:30:23 GMT

So I just want to add some information if you going to program path:

1. in selection screen, you could add VALUE CHECK statement to parameter and it will be checked against foreign key.

2. in dynpro there is a checkbox to do the same..

3. in coding you may try class CL_HRBAS_FOREIGN_KEY_CHECK, or fm: DDUT_INPUT_CHECK.

Re: Data integrity

hagit — Mon, 11 Feb 2019 12:59:32 GMT

I used CL_HRBAS_FOREIGN_KEY_CHECK. But it does not catch the case when foreign key rule is not fulfilled.

Maybe some of the parameters are not sent properly ?

Could you help ?

SPAN { font-family: "Courier New"; font-size: 10pt; color: #000000; background: #FFFFFF; } .L0S31 { font-style: italic; color: #808080; } .L0S32 { color: #3399FF; } .L0S33 { color: #4DA619; } .L0S52 { color: #0000FF; } .L0S55 { color: #800080; } .L0S70 { color: #808080; }

REPORT ZTRY_CHK_FK.



data: lt_ztry_detailed TYPE TABLE OF ztry_detailed

 ,ls_detailed like line of lt_ztry_detailed

 ,lr_fk_chk TYPE REF TO CL_HRBAS_FOREIGN_KEY_CHECK

 ,l_excluded_fields TYPE hrbas_fieldlist_tab

 ,lv_ignore_initial_fields TYPE boole_d

 ,l_message_handler TYPE HRBAS_MSG_HANDLER_IF_REF

 , l_message_handler_cls TYPE REF TO cl_hrbas_message_list

 , lv_ok TYPE boole_d

 .

ls_detailed-bnk1 = 1 .

ls_detailed-bnk2 = 2 .

ls_detailed-bnk3 = 3 .

ls_detailed-key1 = 55 .



refresh l_excluded_fields.

lv_ignore_initial_fields = abap_true. "abap_false. "@@????



CREATE OBJECT lr_fk_chk.

create OBJECT l_message_handler_cls.

l_message_handler ?= l_message_handler_cls.





TRY.

CALL METHOD lr_fk_chk->check_structure

 EXPORTING

 structure = ls_detailed

 excluded_fields = l_excluded_fields

 ignore_initial_fields = lv_ignore_initial_fields

 message_handler = l_message_handler

 IMPORTING

 is_ok = lv_ok

 .

 CATCH cx_hrbas_violated_assertion .

 message 'err' TYPE 'i'.

ENDTRY.



*MODIFY ztry_detailed from ls_detailed.

*insert into table ztry_detailed @@@@@@@@@@@@



ls_detailed-key1 = 2 .

try.

CALL METHOD lr_fk_chk->check_structure

 EXPORTING

 structure = ls_detailed

 excluded_fields = l_excluded_fields

 ignore_initial_fields = lv_ignore_initial_fields

 message_handler = l_message_handler

 IMPORTING

 is_ok = lv_ok

.

 CATCH cx_hrbas_violated_assertion .

 message 'err' TYPE 'i'.

ENDTRY.

Re: Data integrity

DoanManhQuynh — Tue, 12 Feb 2019 03:03:04 GMT

your code is working fine but you have to check the return of lv_ok instead of exception. that class is designed for HR so exception also for HR, you cant get violation there.

Re: Data integrity

hagit — Tue, 12 Feb 2019 12:25:20 GMT

lv_ok is empty in both cases. It is empty when the foreign key rule is ok and also when it is not

Re: Data integrity

DoanManhQuynh — Wed, 13 Feb 2019 04:31:36 GMT

it shouldnt be, I checked with sflight table and it work fine, X if correct and space if incorrect...what about function module i gave you.

Re: Data integrity

Sandra_Rossi — Wed, 13 Feb 2019 06:25:04 GMT

I think that it's not recommended to do a generic check because of the performance. Moreover this class is not released by SAP, so it could be changed or even retired, it may have undesired effects, etc.

Re: Data integrity

hagit — Wed, 13 Feb 2019 09:09:47 GMT

Thank you so much for your help.

You are right. This code works. (My mistake was in the data- I thought that a key existed in the master table but it did not).

The issue is not just checking the FK but also other constrains.
According to my test it seems that check_structure method(in class CL_HRBAS_FOREIGN_KEY_CHECK) does not check if a value is in the range , which is defined in ‘value range’ of the domain. Is it so or something is wrong with my code? For example :
In a domain I declare value range of 1 , 11 .
I use this domain for a table’s field
When checking the value 8 for this field via check_structure, lv_ok is X, even though 8 is not included in the above range