Skip to Content

Foreign Keys HANA

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

Add comment
10|10000 characters needed characters exceeded

1 Answer

  • Posted on May 28, 2015 at 12:36 PM

    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

    Add comment
    10|10000 characters needed characters exceeded