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: 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.
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.
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';
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: : 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: : 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.
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.
DELETE FROM "TEST"."CUSTOMER" WHERE "CUSTID" = '2'; SELECT * FROM "TEST"."ORDER";
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.
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.
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.