cancel
Showing results for 
Search instead for 
Did you mean: 

Presence of HANA statement trigger breaks deferred semantics of foreign key constraint

former_member844843
Discoverer

When I create a statement trigger on a HANA table that has a deferred foreign key constraint, then the constraint works not as a deferred constraint any more, but is checked immediately after, e.g., an INSERT. Tested on HANA cloud and latest HANA express edition.

Has anyone seen this issue?

Steps to reproduce:

-- Create 3 sample tables
CREATE COLUMN TABLE T_1 ("ID" NVARCHAR(5000) NOT NULL, PRIMARY KEY ("ID"));
CREATE COLUMN TABLE T_2 ("ID" NVARCHAR(5000) NOT NULL, "E1_ID" NVARCHAR(5000), PRIMARY KEY ("ID"));
CREATE COLUMN TABLE T_LOG (MSG NVARCHAR(5000));
-- Create foreign key constraint
ALTER TABLE T_2 ADD CONSTRAINT "C__T_2_E1"
FOREIGN KEY ("E1_ID") REFERENCES T_1 ("ID") INITIALLY DEFERRED;
-- Insert referring row before referred row
DO BEGIN
INSERT INTO T_2(ID, E1_ID) VALUES ('1', '1'); -- references row in T_1, which does not exist yet
INSERT INTO T_1(ID) VALUES ('1'); -- inserts row referenced by previous statement
END
-- WORKS


-- Remove data again
DELETE FROM T_2;
DELETE FROM T_1;
DELETE FROM T_LOG;
-- Create (unrelated) statement trigger
CREATE OR REPLACE TRIGGER T_2_TR1 BEFORE INSERT ON T_2 FOR EACH STATEMENT
BEGIN
INSERT INTO T_LOG VALUES ('log');
END;
-- Insert referring row before referred row
DO BEGIN
INSERT INTO T_2(ID, E1_ID) VALUES ('1', '1');
INSERT INTO T_1(ID) VALUES ('1');
END
-- Results in error:
-- Error occurred during SQL query execution
--
-- Reason:
-- SQL Error [461] [HY000]: SAP DBTech JDBC: [461]: foreign key constraint violation: "EF654249DB7148BFA12BCA0F31A02352_3E0KLYC493A8GPDQUXRWDJA5K_RT"."(DO statement)": line 2 col 2 (at pos 10): "EF654249DB7148BFA12BCA0F31A02352_3E0KLYC493A8GPDQUXRWDJA5K_RT"."_SYS_TRIGGER_REF_INS_1805629_#0_#": line 24 col 2 (at pos 1344):

Vitaliy-R
Developer Advocate
Developer Advocate
0 Kudos

Error 461 and `_SYS_TRIGGER_REF_INS` trigger are described in point "25. What are foreign key constraints?" of https://launchpad.support.sap.com/#/notes/2160391

But this error is not thrown if tables are defined as row-based:

CREATE ROW TABLE T_1 ("ID" NVARCHAR(5000) NOT NULL, PRIMARY KEY ("ID"));
CREATE ROW TABLE T_2 ("ID" NVARCHAR(5000) NOT NULL, "E1_ID" NVARCHAR(5000), PRIMARY KEY ("ID"));
CREATE ROW TABLE T_LOG (MSG NVARCHAR(5000));

I would suggest raising this issue to SAP Support.

Regards.

former_member844843
Discoverer
0 Kudos

Interesting observation, thanks. I will raise the issue with SAP.

View Entire Topic
former_member844843
Discoverer
0 Kudos

I got a note from SAP that the bug has been fixed and that the fix will be included in CE2023.16.