Updating tables with foreign key constraints Sex dating site in the city
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).
More details: 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.
Sources: These referential actions define how the constraint is going to act upon UPDATE/DELETE statements.
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.
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.
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.
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.
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.
Search for updating tables with foreign key constraints:
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.