Skip to Content
Author's profile photo Henrique Pinto

Checking out Foreign Keys in HANA SPS5

As of HANA 1.0 SPS5 (officially released through NewDB revision 45), it is now possible to define referential constraints between two tables.

Referential Constraints are also known as Foreign Keys, in the way they define an “external” (foreign) relationship from the referencing table to a referenced table.

More details:

In HANA’s implementation of Foreign Keys, the possible referential actions pretty much follows the SQL Standard definition, with the exception of the “NO ACTION” action.



These referential actions define how the constraint is going to act upon UPDATE/DELETE statements.

I have evaluated how a couple of these referential actions work in HANA.

Below are the findings.

Default Referential Action (RESTRICT)

The “RESTRICT” referential action is the default behavior when creating a new Foreign Key.

Basically, if some statement tries to update/delete a record in a referenced table which is already referenced by some particular record in the referencing table, it won’t be able to be executed.

In order to test them, I created the following tables in HANA.

          CUSTID          INT,
          NAME          VARCHAR(60),
          ADDRESS          VARCHAR(60),
          ORDID          INT,
          CUSTID          INT,
          ORDDATE          DATE,
          NETVAL          DECIMAL(23,2),
          PRIMARY          KEY (ORDID),

Notice that the ORDER table has a referential constraint on the CUSTOMER table, based on the CUSTID column.

Since it has the same name in both tables, I didn’t have to specify the referenced column name.

Also, since I didn’t define any specific referential action, it assumes the default values (RESTRICT for both updates and deletes).

One thing I noticed is that the table definition (opened by double clicking the table name in the Navigator view) does not mention there is a FK in this table at all.

Which sounds kinda flawed to me, since I thought this screen was supposed to tell you everything about that table’s data model.


What you can do is go to “Export SQL” in order to get the SQL statement that originated the table.

In above’s case, this is what it looks like:

           "NETVAL" DECIMAL(23,
          2) CS_FIXED,
           PRIMARY KEY ("ORDID"))

Which leads to the assumption that FKs in HANA are nothing more than a particular case of BTREE indexes.

Alternatively, all the created Foreign Key Constraints can be found in the newly created “_SYS_STATISTICS”.”REFERENTIAL_CONSTRAINTS” table (more details in

Notice they will not appear in the “_SYS_STATISTICS”.”CONSTRAINTS” table, which just contains Primary Key & Unique Key Constraints.




If you haven’t defined a proper name for the FK (e.g. during CREATE TABLE statement), HANA will define an internal name, which can be found in this table, under the “CONSTRAINT_NAME” column. It can be used if you wish to drop the FK but to keep the table content (i.e. not dropping/recreating the whole table).

For the testing, first, I created some customers and orders, and then I tried to create an order that didn’t refer to an existing customer.

INSERT INTO "TEST"."CUSTOMER" VALUES (1, 'Customer 01', '100, A Street 10000 Suncity, CA');
INSERT INTO "TEST"."CUSTOMER" VALUES (2, 'Customer 02', '27 B Street 10001 Suncity, CA');
INSERT INTO "TEST"."ORDER" VALUES (1, 1, '2012-12-01', '100.00');
INSERT INTO "TEST"."ORDER" VALUES (2, 2, '2012-12-01', '200.00');
INSERT INTO "TEST"."ORDER" VALUES (3, 3, '2012-12-01', '300.00');

The result of the last INSERT attempt was an error.


The error message is not very descriptive.

If not knowing the issue beforehand, it could be troublesome finding the root cause.

Could not execute ‘INSERT INTO “TEST”.”ORDER” VALUES (3, 3, ‘2012-12-01’, ‘300.00’)’ in 467 ms 152 µs . SAP DBTech JDBC: [461]: row locked by other transaction: 

The next test is trying to delete a record from the referenced table that is referred in a record in referencing table, i.e. trying to delete a customer which already has some orders created for it.


It also causes an error. This is the error message:

Could not execute ‘DELETE FROM “TEST”.”CUSTOMER” WHERE “CUSTID” = ‘2” in 3.869 seconds . SAP DBTech JDBC: [462]: failed on update or delete by foreign key constraint violation:

Which is much more descriptive than the error message from the forbidden INSERT.

CASCADE Referential Action

Next, I altered the referential action of the FK to “CASCADE”, in order to evaluate its behvaior.

Actually, I dropped the original FK and created an additional one.


One can observe that the type of the Delete Rule has changed by checking the “DELETE_RULE” column in the “REFERENTIAL_CONSTRAINTS” table.


Then I repeated the same tests as before.

First, for the forbidden INSERT, the error message was the same.


For the DELETE, however, since the new Delete Rule is “CASCADE”, we get a different behavior.

When trying to delete a Customer which already has some orders created for it, HANA will cascade the deletion statement to the referencing orders, i.e., it will delete the customer and all orders of that customer.




In this case, Order #2, which referred Customer #2, was deleted from the “ORDER” table.

“IMPORT FROM” Statement

Lastly, I wanted to test the behavior of FKs in HANA when importing data from bulk load tools (e.g. SLT, DS & “IMPORT FROM” statement).

Unfortunately, I don’t have a SLT instance connected to this HANA rev45 box, hence I won’t be able to test it.

As for Data Services, I’ll run some tests later and update this blog with the findings.

When trying to import data from a text file (e.g. in CSV format), one can use the native HANA bulk loading command, the “IMPORT FROM” statement.

It is documented here:

It was really simple, I just create a .csv file with three rows equivalent to the ones I had inserted above in the “ORDER” table.

The .csv also included the column names.


I also deleted all entries from the “ORDER” table in order to avoid Primary Key constraints to fail the insert.

Then, I ran the Import command:


However, no rows were added.

This can be seen in the result screen below.

Strangely enough, I didn’t get any error messages, the statement had apparently executed without any issues.

The only hint that maybe it hadn’t worked as expected was the “Rows Affected: 0” part after the IMPORT statement.


So, apparently, when some record in the file to be loaded does not meet some FK constraint, the whole file is ignored!

To be honest, I suspect it will ignore the whole batch per each commit (if I use the “BATCH <n>” option – since I didn’t use it, I only get one batch by default).

Anyway, this is definitely not the expected behavior.

I hope there is a way around this, or that there will be one shortly, ideally, saving the failed entries in a error log file or something, but inserting the correct ones anyway.

And, just to make sure the failed loading was indeed due to the FK, I inserted Customer #3 in the “CUSTOMER” table, hence making the Order #3 entry correct from a referential point of view.

INSERT INTO "TEST"."CUSTOMER" VALUES (3, 'Customer 03', '215 Main Street 10010 Suncity, CA');

Running the same test as above, we finally get the expected entries in the Order table, hence proving the failed loading was indeed due to the FK.


I’ll update this document soon with loading tests of tables with FKs through Data Services.


As stated during TechEd 2012 Keynotes, sessions and lectures, HANA Support Package Stack 05 was mainly aimed at turning it into a fully transactional DB, instead of just focusing on Data Warehouse/Analytic functionalities as the earlier SPSs. As of now, HANA can truly be both a real time analytic Data Warehouse for BI reports and advanced analytic scenarios as well as a high-end performance relational DB for real time applications.

One of the functionalities in HANA SPS5 that makes it possible is exactly the enablement of Foreign Keys. While many people may argue that a well-designed application won’t ever depend on referential integrity in the Database level, it is a fact that several smaller & simpler applications do rely on FKs for that, be it for performance or for development simplification. By enabling FKs natively in the DB level, HANA makes it possible for easy migration of custom built or 3rd-party applications from other DBs to HANA. This is well aligned with SAP strategy for HANA, as the articles below indicate.

It is awesome to see SAP quickly closing the gap between HANA and more mature DB systems in the market.

For those of us used to the old-school pace of SAP development releases, it is almost unbelievable to see a new HANA revision each other week.

However, while innovating at large scale, SAP still makes sure that the HANA implementations and short-term benefits are actually achievable through the release of simple yet mandatory functionalities such as this one. It just shows how much SAP is committed to non-disruptive innovation.

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Andy Silvey
      Andy Silvey

      Hi Henrique,

      another nice blog,



      Author's profile photo Former Member
      Former Member

      Hi Henrique

        Thanks for updating us with the new features of SPS05 which is really needed.Looking for more updates.


      Santosh Varada

      Author's profile photo Former Member
      Former Member

      Hi Henrique,

      I am now running into a similar error on 'insert' . I have my foreign keys defined with default rules(RESTRICT). So, do you think the only solution to this is updating my foreign key constraint to have 'CASCADE' rule?

      SAP DBTech JDBC: [461]: row locked by other transaction



      Author's profile photo Henrique Pinto
      Henrique Pinto
      Blog Post Author

      No, not at all. If you are getting that error on insert, it means you're trying to do a forbidden insert, for example, in my example above, creating an order with an non-existing customer id. If you had read carefully the content above (and assuming the behavior hasn't changed in the latest revisions - which I have not tested yet, but what I would assume it hasn't), then you'd see both RESTRICT and CASCADE referential actions have this same behavior (and same error message) for a forbidden insert.

      If that was not your intent, i.e. you don't want to forbid this kind of insert, i.e. you want to allow the creation of any new order even if the customer id does not exist, then you should not be using any foreign keys. Just drop them.

      Author's profile photo Former Member
      Former Member

      Hi Henrique,

      Do you know if there is some way to create a foreign key using XS syntax?

      I'm working in a project that user Hana XS to version it the code, but I can't found any stuff about How to create a foreign key using Hana XS syntax(based uppon JSON).

      I'll give a short example:

      table.schemaName = "HB";

      table.tableType = COLUMNSTORE;

      table.description = "Pessoa Teste";

      table.columns = [

                {name = "id"; sqlType = BIGINT; nullable = false; comment = "Primay Key"; },

                {name = "name"; sqlType = NVARCHAR; nullable = false; length = 100; comment = "Name"; },

                {name = "idDepto; sqlType = BIGINT; nullable = true;"}


      table.primaryKey.pkcolumns = ["id"];

      Thanks and Regards

      Author's profile photo Former Member
      Former Member

      Hello again,

      I found something interesting in Modeling Guide.

      Looks like that in SP6 the foreign key definition doesn't exists.

      struct TableDefinition {

      string SchemaName;

      optional bool temporary;

      optional TableType tableType;

      optional bool public;

      optional TableLoggingType loggingType;

      list<ColumnDefinition> columns;

      optional list<IndexDefinition> indexes;

      optional PrimaryKeyDefinition primaryKey;

      optional string description


      Author's profile photo Henrique Pinto
      Henrique Pinto
      Blog Post Author

      Have you tried to play with the "indexes" parameter?

      In HANA, FKs are implemented as special indexes.

      Also, the Developer Guide mentions that, from a development perspective, associations can reflect FK-like relationships. Check the item " OData Associations".

      Author's profile photo Priya Jha
      Priya Jha

      Hi Henrique,

                       While we have the foreign key defined for a table, and we use DELETE in the parent table, will it cause deletion for that particular record, in the child table also?

      Please guide.



      Author's profile photo Henrique Pinto
      Henrique Pinto
      Blog Post Author

      Hi Priya,

      as explained above, it will if you set the Cascade option for DELETE.
      The documentation URLs have changed since I wrote this blog above, search for FOREIGN KEY in this page:

      CREATE TABLE - SAP HANA SQL and System Views Reference - SAP Library

      Author's profile photo Former Member
      Former Member

      Hi Henrique,

      Thanks for taking time and narrating in detail. While executing I came across fallowing changes below.

      select * from "_SYS_STATISTICS"."CONSTRAINTS" where schema name = 'TEST';

      select * from "_SYS_STATISTICS"."REFERENTIAL_CONSTRAINTS" where schema name = 'TEST';

      Changes it to 

      select * from "SYS"."CONSTRAINTS" where schema name = 'TEST';

      select * from "SYS"."REFERENTIAL_CONSTRAINTS" where schema name = 'TEST';


      Author's profile photo Lars Breddemann
      Lars Breddemann

      The question is not quite clear.

      Do you want to import (referential) constraints via a data load into those system views?

      That's not possible. You have to go through DDL for that.

      Author's profile photo Former Member
      Former Member

      Hi Lars,

      Thanks for the update.