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
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