Hi all,
I'm trying to materialize a real foreign key relationship as a computed
two-column id, parent_id, for reasons of backward compatibility. I have two
tables joined by an FK relationship...
create table parent(key1 int, key2 int,
primary key(key1, key2));
create table child(key1 int, key2 int, casc1 int,
primary key (key1, key2, casc1),
foreign key(key1, key2) references parent(key1, key2) on update cascade
on delete cascade);
Now I create an artificial id column in parent and child...
alter table parent add column id bigint auto_increment
alter table child add column id bigint auto_increment
I now want to add a computed column "parent_id" in table child that holds
values for parent.id as per the child FK on key1 and key2, but I get
"scalar subquery returned more than one row" errors when i try the
following...
alter table CHILD add column parent_id bigint as (select p.id from parent p
where p.key1 = key1 and p.key2 = key2)
because (I think) the query is treating the raw key1 and key2 refs as refs
to the column in parent rather than the column in child and so returns all
the rows from parent.
Is there any way to be explicit? For example
alter table CHILD add column parent_id bigint as (select p.id from parent p
where p.key1 = child.key1 and p.key2 = child.key2)
If not, is there any other way I can generate a unique fk relationship of
this type based in the existing fk? I was thinking maybe concatenating and
hashing the key1 and key2 cols, but I worry about the risk of collision if
I do that?
Note that I can't take the obvious route and rename the key1 and key2
columns in child - it is required that they be the same column names as in
parent.
Cheers,
Ian.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
