Updating tables with foreign key constraints
Then, I ran the Import command: However, no rows were added. 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.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.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.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.
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.
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.
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.
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.