Foreign Keys HANA

Hi Claudia,

1) nope - up to SPS 9 there is no DISABLE/ENABLE option for referential constraints (AFAIK)

2) Yes, that's possible.

3) Sure - why wouldn't it?

Consider this example:

create column table father (id integer, name varchar(50), family varchar(20),
                            primary key (id, family));


create column table mother (id integer, name varchar(50), family varchar(20),
                            primary key (id, family));
                            
create column table child (id integer, name varchar(50), family varchar(20),
                          father_id int,
                          mother_id int,
                       primary key (id),
                        foreign key (father_id, family) references father (id, family) on delete set null,
                        foreign key (mother_id, family) references mother (id, family) on delete set null);

create column table father2 (id integer, name varchar(50), family varchar(20),
                            primary key (id, family));

alter table child add foreign key (father_id, family) references father2 (id, family) on delete set null;

insert into father values (1, 'FATHER_1', 'MILLERS');
insert into father2 values (1, 'FATHER_2', 'MILLERS');
insert into mother values (1, 'MOTHER_1', 'MILLERS');
insert into child values (1, 'ALEX', 'MILLERS', 1, 1);

select c.id, c.name, m.name as MOTHER_NAME, f.name as FATHER_NAME, f2.name as FATHER2_NAME
from
    child c 
    inner join mother m
            on (c.mother_id, c.family) = (m.id, m.family)
    inner join father f
            on (c.father_id, c.family) = (f.id, f.family)
    inner join father2 f2
            on (c.father_id, c.family) = (f2.id, f2.family);

ID NAME MOTHER_NAME FATHER_NAME FATHER2_NAME

1 ALEX MOTHER_1 FATHER_1 FATHER_2

update father2 set name = 'JOE' where id =1 ;

ID NAME MOTHER_NAME FATHER_NAME FATHER2_NAME

1 ALEX MOTHER_1 FATHER_1 JOE

- Lars