Updating tables with foreign key constraints

Posted by / 23-Mar-2019 10:54

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.

updating tables with foreign key constraints-60updating tables with foreign key constraints-34updating tables with foreign key constraints-68

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.

One thought on “updating tables with foreign key constraints”

  1. The reception area was encased in glass with the most beautiful glass ceiling. I couldn’t have pulled all this off without the amazing Claire Woods from Fields of Colour. I bought this old wooden door, exactly as is, in a junk yard in Cape Town.

  2. But instead of hitting a casino once in a while and playing the slots for fun, people are using them to make the most important decision of their lives: who their future partner is.” Now, this isn’t a “Death to all dating apps! Online dating allows you to play the field from the comfort of your own home and without having to engage with people, says Chris Armstrong, except what they write.