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 a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

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 a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.