No check on foreign key in ADT-defined database table

[nicknf]

Dear community,

I tried out a tutorial to define a foreign key definition) (until step 11). The defined table looks like this:

@EndUserText.label : 'Table of Bank Accounts'
@AbapCatalog.enhancementCategory : #EXTENSIBLE_CHARACTER_NUMERIC
@AbapCatalog.tableCategory : #TRANSPARENT
@AbapCatalog.deliveryClass : #A
@AbapCatalog.dataMaintenance : #NOT_ALLOWED
define table zaccounts {
  key client         : abap.clnt not null;
  key account_number : abap.numc(8) not null;
  @AbapCatalog.foreignKey.keyType : #KEY
  @AbapCatalog.foreignKey.screenCheck : false
  bank_customer_id   : /dmo/customer_id not null
    with foreign key [0..*,1] /dmo/customer
      where customer_id = zaccounts.bank_customer_id;
  bank_name          : z_bank_name;
  city               : /dmo/city;
  @Semantics.amount.currencyCode : 'zaccounts.currency'
  balance            : abap.curr(16,2);
  currency           : /dmo/currency_code;
  account_category   : abap.numc(2);
  lastchangedat      : timestampl;

}

The foreign key-related table /dmo/customer is empty in my system. Thus I should not be possible to insert a new line to zaccounts. However, I can add a line with the following code:

    DATA myzaccounts type zaccounts.
    DELETE FROM zaccounts.
    myzaccounts-account_number = `myaccount`.
    myzaccounts-bank_customer_id = 12345.
    INSERT zaccounts FROM @myzaccounts.

The code runs without an error and I can see the new line in zaccounts. Shouldn't the insert fail with a foreign key constraint violation?Am I missing something?

Thanks for your help!

[nicknf]

So I conclude two things: (1) The language to create a DDIC database table using ADT does not have a name (2) nor does it support classical foreign key constraints as used in RDBMS for decades?

[horst_keller]

(1) The language to create a DDIC database table using ADT is named Dictionary DDL.

(2) It supports the definition of foreign key relationships as it was possible in SE11 for decades.

[AndreaUS]

Value Helps in RAP are defined with Consumption.ValueHelpDefinition annotations as described here: Providing Value Help for the Selection Fields | SAP Help Portal

Foreign key relationships are evaluated by the RAP framework, for example, during a read-by-association operations and in the ON condition of associations.

[nicknf]

So this means foreign key relationships are exclusively modelled in views when using RAP, not on database level?

[AndreaUS]

Do you mean foreign keys for value helps? Yes. Value helps are modeled exclusively in views for RAP. But the foreign keys defined in ABAP Dictionary are evaluated as well in some scenarios.

[nicknf]

I was referring to database foreign keys, not to value helps. How can access one the ABAP dictionary when developing with ADT and SAP BTP ABAP Cloud service instance? The only way I am aware of is creating a new database table in ADT. This does not seem to allow foreign key constraints as discussed in this thread. Thanks for your time discussing this!

[horst_keller]

DDIC foreign keys are not considered in ABAP SQL.

https://help.sap.com/doc/abapdocu_latest_index_htm/latest/en-US/index.htm?file=abenddic_database_tab...

[nicknf]

@horst_kellerThanks for your reply!

But how can a foreign key relationship be modelled on database level in the context of BTP and RAP? If I am not mistaken there is only one option in the ADT to create a new database table (which is the one I used).

Or does this mean that in RAP no foreign key relationships are modelled on database table level but only on higher levels in the virtual data model? The showcase app suggests this, since relationships (associations and compositions) are only modelled in the CDS views and not in the database tables.

[horst_keller]

Let me try to explain some basic facts.

In ABAP we have two levels of metadata definitions:

• ABAP Dictionary (DDIC)
  The old one. Here you can define global datatypes, classic DDIC database tables and DDIC database views. The tools are transaction SE11 and nowadays ADT. In ADT, a DDIC DDL is used that in the end does the same as transaction SE11. DDIC database tables can have a semantic property called foreign key relationship. The evaluation of that property is done by frameworks such as classic dynpros, classic database locks but not in the ABAP language (ABAP SQL) itself. ABAP SQL knows nothing of foreign keys.
• ABAP CDS
  The new one. ABAP CDS is still largely based on DDIC. Up to now, you can define some types (but no structures) and CDS entities such as CDS Views, CDS Table Functions, CDS Hierarchies, ... that access DDIC database tables. The relations between CDS entities are modelled by CDS associations that are instantiated as physical joins when accessed in a path expression. It depends on the framework using the CDS entities, if the foreign key relations of the underlying DDIC database tables are considered at all. Up to now there are no CDS table entities available in ABAP CDS. That means, you cannot define an ABAP-managed database table directly with ABAP CDS. Once, this will be available, a concept regarding foreign key relationships between CDS table entities might become available too, probably based on associations then. 

In the tutorial, you were working on a DDIC database table, whose properties are described here:

https://help.sap.com/doc/abapdocu_cp_index_htm/CLOUD/en-US/index.htm?file=abenddic_database_tables.h...

CDS entities are described here:

https://help.sap.com/doc/abapdocu_cp_index_htm/CLOUD/en-US/index.htm?file=abencds_entities.htm

And finally, RAP is a programming model based on CDS described here:

https://help.sap.com/doc/abapdocu_cp_index_htm/CLOUD/en-US/index.htm?file=abenabap_rap.htm

BTP is a SAP product using these technologies. 

ADT is just a tool. It does not make sense to speak about "ADT database tables" or to say "access the ABAP dictionary when developing with ADT". You can edit DDIC objects with ADT and access those in ABAP CDS, ABAP language and with frameworks built with ABAP.

[nicknf]

Sorry for my late reply and thanks for your extensive answer:

So means in a nutshell, when I create a database table in ADT, it is created using DDIC, correct?

If so this would mean when using ADT I do use DDIC but - however, can not make use of the foreign key relationship feature of DDIC. Is this correct?

[horst_keller]

You still mix the things up.

DDIC (short for ABAP Dictionary) is a metadata repository.

There are two tools for editing metadata such as DDIC database tables of the ABAP Dictionary:

• SE11 in SAP GUI
• Source code based editors as well as from based editors in ADT.

"when I create a database table in ADT, it is created using DDIC," is not correct.

Correct is: "you can use ADT (as well as SE11) to create a DDIC database table".

In fact, the source code that you see in ADT isn't even stored as such but created temporarily from the metadata in the ABAP Dictionary.

And also " using ADT I do use DDIC but - however, can not make use of the foreign key relationship feature of DDIC" simply makes no sense,

ADT is a tool for creating repository objects, not for using them. You use a DDIC database table in ABAP language, in ABAP CDS, in classic dynpros, etc. It depends on the usage in these frameworks, whether a foreign key relationship is considered, not on ADT.

[nicknf]

Ok. Thanks for the explanation.So what is the name of the source code to define a table in ADT?

[horst_keller]

There is no name of the source code to define a DDIC database table in ADT. The source code is created on the fly when you call the editor and deleted after editing.

https://help.sap.com/doc/abapdocu_latest_index_htm/latest/en-US/index.htm?file=abenddic_tools.htm