Data integrity

[hagit]

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

[matt]

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.

[hagit]

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

[matt]

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.

[Sandra_Rossi]

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

[hagit]

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

[matt]

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.

[hagit]

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.

[matt]

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.

[hagit]

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

[matt]

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.

[hagit]

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?

[matt]

"Is there something similar in ABAP?"

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

[DoanManhQuynh]

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.

[hagit]

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.

[DoanManhQuynh]

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.

[hagit]

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

[DoanManhQuynh]

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.

[Sandra_Rossi]

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.

[hagit]

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

[DoanManhQuynh]

thats wierd too, i checked with bkpf-bstat, it still get correct value. basically it would be checked whatever it is checked table or fixed value.

[hagit]

Sandra,

Wasn't this class released by Sap?

[hagit]

Quynh Doan Manh,

Just to highlight: The wrong value 8 is entered without any problem also in SM30.

What do you mean by “it still get correct value” ? Do you mean that lv_ok should get X when value is 1 / 11 but should get space for the other values?

It is not working for me:

From the debugger:

[DoanManhQuynh]

yes that what i mean. it hard for me to say why you dont get expected rersult since you using your custom structure and domain but i tested with standard table (here is BKPF and BSTAT field) and it always return correctly...

[hagit]

I want to give points for some of yours comments. How can I do it?

[matt]

You can't vote on comments. Only "like".

[DoanManhQuynh]

point is good motivation for community but i think right answer or close answer with solution is much more better. I think you could tell us what did you do with your question.