foreign key to a materialized view (to solve child with two parents)
Hi everybody,
I'd like to know what you think about this approach.
Let's imagine table A1 and table A2 with the same columns as PK. The intersection of A1.pk with A2.pk is empty. These two tables should be only one table (called A), but they are two different ones because a design error. There is another table called B (and many more) with records that are child of the table called A (A1+A2). There's no foreign key because it's not posbile.
It`s too late to change the design, but I want to avoid having records in B with no parent either on A1 or A2. So the idea is to build a materailzed view (called A_mview) with fast refresh on commit as "select pk from A1 union all select pk from A2". Then to add a PK to A_mview (same columns as A1 or A2) and create two FKs to A_miew, one on A1 and the second one on A2.
So I would like to know what advantages/disadvantages of this idea you can think.
I see that the parent/child integrity will be enforce at commit time wich is not immediate.
I see that if for any reason the mview become stale there can be new parents with no right to have child records.
Any thoughts on this?
Thanks.
Joaquin Gonzalez
Edited by: user4070490 on 17-dic-2009 7:31