cancel
Showing results for 
Search instead for 
Did you mean: 

Foreign Keys HANA

CL1
Associate
Associate
0 Kudos
1,485

Hi,

I´ve got some questions in the context of foreign keys in HANA:

Is it possible to disable foreign key constraints without having to delete and recreate them?

Is it possible to have more than one foreign key including the same field of a table?

Is it possible to update a column in a referenced table when the column is not part of a foreign key?

Thanks, Claudia

Accepted Solutions (0)

Answers (1)

Answers (1)

lbreddemann
Active Contributor
0 Kudos

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