Can we create self referencing foreign key in SAP HANA?

[Former Member]

Hi Experts,

I am executing below commands for creating self referencing foreign key in SAP HANA.

I got the below two commands from SAP Help Portal.

CREATE TABLE SELF(A INTEGER PRIMARY KEY, B INTEGER);

ALTER TABLE SELF ADD CONSTRAINT FK_T1 FOREIGN KEY(B) REFERENCES SELF(A);

While executing above commands, the first command(i.e Create command) is working properly.

But Alter command is giving below error:

Could not execute 'ALTER TABLE SELF ADD CONSTRAINT FK_T1 FOREIGN KEY(B) REFERENCES SELF(A)' in 3 ms 76 µs .

SAP DBTech JDBC: [7]: feature not supported: cannot create foreign key constraint referencing the same table

Can anyone help me out with the above error.

I tried executing the above commands in both SAP HANA SPS 8 as well as SPS 9 but then also I am getting the above error.

Regards,

Jimit Doshi

[ruediger_karl]

As already mentioned, SPS10 supports self-referenced FK constraint, but also cyclic FK constraint.

Regards, Ruediger

[lbreddemann]

Well, the error message (for once) is clear here: it's not supported to do that.

If you really want to implement such a semantic, you might want to look into triggers.

- Lars

[Former Member]

Hi ,

Created trigger to implement self referencing foreign key in SAP HANA(SpS8 & SPS9) but got below error:

Could not execute 'create trigger self_Constraint_chk AFTER insert on self REFERENCING NEW ROW INSERTED BEGIN Declare ...' in 8 ms 390 µs .

SAP DBTech JDBC: [7]: feature not supported: select on the subject table (JIMIT.SELF) inside a trigger is not allowed

Create Table Script:

CREATE TABLE SELF(A INTEGER PRIMARY KEY, B INTEGER);

Trigger Code:

create trigger  self_Constraint_chk

AFTER

insert

on  self

REFERENCING  NEW ROW INSERTED 

BEGIN

Declare val integer;

DECLARE CUSTOMCONDITION CONDITION FOR SQL_ERROR_CODE 10001;/* Custom Error Code = 10001*/

--SET NOCOUNT ON ;

select count(1) into val from self where a = :INSERTED.b;

if val = 0 then

SIGNAL CUSTOMCONDITION SET MESSAGE_TEXT = 'Constraint violation exception!';

end if;

END;

Can you help me out with the above error.

Regards,

Jimit Doshi

[lbreddemann]

Ahw... right... forgot about that  😕

In that case, you cannot really implement this constraint on table level.

The remaining option is to cover the update/insert into a procedure that does the checking for you.

- Lars

[Former Member]

Hi Lars,

Can u give me an example of how could you implement self referencing foreign key with the help of a  procedure?

Regards,

Jimit Doshi

[lbreddemann]

Well, the point here is that all data change access must be done by your procedure (no direct INSERT/UPDATE/DELETE). Then you can check whatever you like in the procedure and only perform the data change if it matches your rules.

BTW: just tried out the self-referencing constraint in SPS 10 and that works w/o issues.

- Lars

[Former Member]

Hi Lars,

Is SAP HANA SPS10 generally available for customers?

Regards,

Jimit Doshi

[lbreddemann]

Yep. It's on SAP Service Marketplace.

- Lars