Skip to Content

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: http://en.wikipedia.org/wiki/Foreign_key

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.

/wp-content/uploads/2012/12/update_delete_rules_162156.png

Sources:

http://help.sap.com/hana/html/sql_create_table.html#create_table_contents_source

http://help.sap.com/hana/html/sql_alter_table.html#alter_table_add_constraint_claus

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.

DROP TABLE "TEST"."CUSTOMER";
CREATE COLUMN TABLE "TEST"."CUSTOMER" (
          CUSTID          INT,
          NAME          VARCHAR(60),
          ADDRESS          VARCHAR(60),
          PRIMARY KEY (CUSTID)
          );
DROP TABLE "TEST"."ORDER";
CREATE COLUMN TABLE "TEST"."ORDER" (
          ORDID          INT,
          CUSTID          INT,
          ORDDATE          DATE,
          NETVAL          DECIMAL(23,2),
          PRIMARY          KEY (ORDID),
          FOREIGN KEY (CUSTID) REFERENCES "TEST"."CUSTOMER"
          );

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.

/wp-content/uploads/2012/12/table_definition_order_162226.png

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:

CREATE COLUMN TABLE "TEST"."ORDER" ("ORDID" INTEGER CS_INT,
           "CUSTID" INTEGER CS_INT,
           "ORDDATE" DAYDATE CS_DAYDATE,
           "NETVAL" DECIMAL(23,
          2) CS_FIXED,
           PRIMARY KEY ("ORDID"))
;
CREATE BTREE INDEX "_SYS_CONSTRAINT_810121_#0_#F0" ON "TEST"."ORDER" ("CUSTID" ASC)

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 http://help.sap.com/hana/html/_r_e_f_e_r_e_n_t_i_a_l__c_o_n_s_t_r_a_i_n_t_s.html).

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

SELECT * FROM "_SYS_STATISTICS"."CONSTRAINTS" WHERE SCHEMA_NAME = 'TEST';
SELECT * FROM "_SYS_STATISTICS"."REFERENTIAL_CONSTRAINTS" WHERE SCHEMA_NAME = 'TEST';

/wp-content/uploads/2012/12/statistics_constraints_pk_162157.png

/wp-content/uploads/2012/12/statistics_constraints_fk_162227.png

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.

/wp-content/uploads/2012/12/sql_editor_insert_error_162225.png

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.

DELETE FROM "TEST"."CUSTOMER" WHERE "CUSTID" = '2';

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.

ALTER TABLE "TEST"."ORDER" DROP CONSTRAINT "_SYS_CONSTRAINT_810121_#0_#F0";
ALTER TABLE "TEST"."ORDER" ADD CONSTRAINT "FK" FOREIGN KEY (CUSTID) REFERENCES "TEST"."CUSTOMER" ON DELETE CASCADE;

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

/wp-content/uploads/2012/12/statistics_constraints_fk_cascade_162228.png

Then I repeated the same tests as before.

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

/wp-content/uploads/2012/12/sql_editor_insert_error_cascade_162229.png

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.

DELETE FROM "TEST"."CUSTOMER" WHERE "CUSTID" = '2';
SELECT * FROM "TEST"."ORDER";

/wp-content/uploads/2012/12/sql_editor_delete_ok_162230.png

/wp-content/uploads/2012/12/sql_editor_delete_result_162231.png

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: http://help.sap.com/hana/html/sql_import_from.html.

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.

/wp-content/uploads/2012/12/order_csv_162232.png

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:

DELETE FROM "TEST"."ORDER";
IMPORT FROM CSV FILE '/home/test/order.csv' INTO "TEST"."ORDER" WITH RECORD DELIMITED BY '\n' FIELD DELIMITED BY ';';
SELECT * FROM "TEST"."ORDER";

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.

/wp-content/uploads/2012/12/sql_editor_import_from_error_162237.png

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.

/wp-content/uploads/2012/12/sql_editor_import_from_ok_result_162238.png

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

Conclusion

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.

http://www.saphana.com/community/blogs/blog/2012/11/01/sap-hana-opens-sql-interfaces-for-custom-developed-apps

http://www.saphana.com/community/blogs/blog/2012/12/03/3rd-party-bi-certification-program-for-sap-hana

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.

To report this post you need to login first.

12 Comments

You must be Logged on to comment or reply to a post.

    1. geetha baki

      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


      thanks,

      Geetha

      (0) 
      1. Henrique Pinto 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.

        (0) 
  1. Felipe Antonio Maria

    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

    (0) 
    1. Felipe Antonio Maria

      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

      };

      (0) 
      1. Henrique Pinto 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 “7.1.6.6 OData Associations”.

        (0) 
  2. 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.

    Thanks

    Priya

    (0) 
  3. satish chowdary

    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’;

    Tq

    (0) 
    1. 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.

      (0) 

Leave a Reply