HDBTABLE concept - Foreign Key

[Former Member]

Hi All,

We are using SP8 version of HANA and created all the tables using hdbtable concept. Now we have a requirement to give foreign key relationships between the tables. I found in many posts that foreign key relationships is currently not supported and as an alternative people are asking to create a procedure and write the code. But in developer guide I saw an option to give foreign key constraints as below. When i try to create an .xsodata object and activate it with the code, it is not generating any constraint. I performed test to check the referential integrity, But it did not work. Please let me know if I am missing any  setting or step.

Code used :

service { "sample.odata::customer" as "Customers"; "sample.odata::order" as "Orders"; association "Customer_Orders" principal "Customers"("ID") multiplicity "1" dependent "Orders"("CustomerID") multiplicity "*"; }

Note : Code wise, there is no problem for me with syntax.

[thomas_jung]

Sorry, but the syntax and development artifact you found has nothing to do with defining foreign keys in the database.  XSODATA is for creating an ODATA REST service.  The syntax you mention has to do with the relationship between entities in the service interface.

As a note, in CDS/HDBDD you can also define associations.  They can be used to define relationships within CDS views.  They also do not create foreign keys in the database. However they are used to enforce relationships when use the XSDS library to perform your CUD operations.

[Former Member]

Hi Thomas,

Thanks for quick reply. As I am new to SP8 world and not familiar with CDS concepts, Could you please help me on how to define the foreign key relationships.

Note : We are not using any modelling. Using HANA just as a database. So for data loads we want to ensure referential integrity.

I have found that syntax in SP8 developer guide.

[Former Member]

Hi Thomas,

Thanks for the clear answer.

Just to confirm: In short you are saying there is no way to expose the following tables:

type PersonT{

     name : String;

}

type AddressT{

     streetNumber : Integer;

     streetName : String;

     person : PersonT;

}

As an xsodata READ service "Persons" such that on query it would return a struct like this one(?):

{

  "name": "Hugo",

  "Addresses": [

  {"streetNumber: "1", "streetName": "A"},

  {"streetNumber: "2", "streetName": "B"}

  ]

}

Thanks!

Hugo

[thomas_jung]

Sure you can do that. That's exactly what an xsodata association will create. Of course to get the addresses inlined you will have to use the $expand option when calling the service. But if you have a navigation association and expand it the child records will be returned with the parent. This does not in any way create a foreign key in the database however (your original question).

[Former Member]

Hi,

So, this is what I understand - associations is not equivalent to a foreign key constraint which actually implements the referential integrity check.

Sorry to re-iterate it again, but I am a bit shocked that the constraint is not included in the hdbtable syntax. I see that we can use foreign key constraints when we create table via sql scripts and not as hdbtables. The constraint is quite ubiquitous in the relational database world. Is there a specific reason why it is ignored in hdbtables?

[thomas_jung]

>Sorry to re-iterate it again, but I am a bit shocked that the constraint is not included in the hdbtable syntax.

All I can say is that this is an intentionally design decision by SAP to not make associations act as constraints or to include constraints in the CDS syntax.