Skip to Content
0
Jan 26 at 12:23 PM

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

69 Views

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):