Updating tables with foreign key constraints
One can observe that the type of the Delete Rule has changed by checking the “DELETE_RULE” column in the “REFERENTIAL_CONSTRAINTS” table. 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.
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).
Sources: These referential actions define how the constraint is going to act upon UPDATE/DELETE statements.
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 ” option – since I didn’t use it, I only get one batch by default).
It can be used if you wish to drop the FK but to keep the table content (i.e. 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'); Which is much more descriptive than the error message from the forbidden INSERT.
I have evaluated how a couple of these referential actions work in HANA. The “RESTRICT” referential action is the default behavior when creating a new Foreign Key.