topic Re: foreign keys in open sql in Application Development and Automation Discussions

foreign keys in open sql

Former Member — Thu, 20 Aug 2009 08:18:52 GMT

Hello everybody.

I defined foreign keys between tables in ABAP dictionary and wrote Open SQL code in ABAP, which worked with this tables.

This code changed values in column, which was defined as foreign key. New value, which wasn't in check table, was successfully inserted and even sy-subrc was equal to 0. I found this problem on forums and found 2 workarounds: 1) use explicit checks on check table before updating. 2) use Native SQL to define foreign keys. Both solutions aren't good for me. How can we turn on implicit checks on foreign keys?

Thanks.

Re: foreign keys in open sql

Former Member — Thu, 20 Aug 2009 08:35:46 GMT

Hi,

Reasons for an UPDATE statement failure

If CONSTRAINT definitions exist for base tables in which rows were updated with the UPDATE statement, each row that was updated is checked against the CONSTRAINT definitions. The UPDATE statement fails if this is not the case for at least one modified row.

For each row in which the value of foreign key columns has been updated with the UPDATE statement, the database system checks whether the respective resulting foreign key exists as a key or as a value of an index defined with UNIQUE (see CREATE INDEX statement) in the corresponding referenced table. The UPDATE statement fails if this is the case for at least one modified row.

For each row in which the value of a referenced column of a referential CONSTRAINT definition is to be updated using the update statement, the database system checks whether there are rows in the corresponding foreign key table that contain the old column values as foreign keys. The UPDATE statement fails if this is the case for at least one row.

If triggers that are to be executed after an UPDATE statement were defined for base tables in which rows are to be updated with the UPDATE statement, they are executed accordingly. The UPDATE statement will fail if one of these triggers fails.

Regards,

Shamma

Re: foreign keys in open sql

Former Member — Thu, 20 Aug 2009 08:46:08 GMT

Hi,

If your data is coming through a screen and the screen fields are linked to data elements/domains, then the screen will take care of the foreign field check. if you are directly updating the database tables through a program which doesnot involve screens or screen fields which do not refer to the data elements/domains there will not be any check in this case. so try as said above.

Hope this will serve your purpose.

Cheers

Ramc

Re: foreign keys in open sql

Former Member — Thu, 20 Aug 2009 09:28:28 GMT

Check doesn't work both in INSERT and UPDATE statements in Open SQL. I tried it 5 minutes ago. I created foreign key correctly because If I try to add wrong value on screen, system checks check table and doesn't insert it.

Re: foreign keys in open sql

Former Member — Thu, 20 Aug 2009 10:22:01 GMT

Hi,

SQL statements are not implicitly checking foreign key relationships; the application logic must take care of this.

Never manipulate SAP standard tables directly via own program code.

Integrity is turned OFF for Open SQL statements but is turned ON for screen fields.

You can explicitly assign different input help or search help for certain screen field independently of type it is referring i.e. by means of fm F4IF_FIELD_VALUE_REQUEST.

This however still relates only to screen fields, not fields used directly in ABAP statement.

try this function module..

HELP_VALUES_GET_WITH_CHECKTAB

Regards,

Shamma

Re: foreign keys in open sql

Former Member — Thu, 20 Aug 2009 11:21:49 GMT

Everything you wrote in your message I had written in my question. I know that I have the opportunity to make an explicit check. All I want to know if there is a chance to turn on an implicit check.