Re: Foreign key to multiple tables ???

Kirmo Uusitalo wrote:
>
> In article <32A5C09F.2234_at_us.oracle.com>,
> Oracle Education <user_at_us.oracle.com> wrote:
>
> >You can using a trigger.
>
> A trigger won't disallow dropping a referenced table, and it doesn't quarantee
> data integrity the way foreign key constraint does.
>

I don't see why you can't code the trigger to guarantee data integrity. You would need a trigger on the child table and on the parent table(s). The trigger on the parent table(s) would check on delete/update to see if the column is referenced in the child table. On the child table you would determine which column should be the parent and then check to see if it exists. In both cases when your constraint would be violated just raise the same Oracle error that a normal FK constraint would raise.

I can't see how to stop a referenced table from being dropped. Anyway I don't allow tables to be dropped causually. In this case I would probably code up a PL/SQL procedure to drop the table. It would first check to see if there are any foreign key references before dropping the table.

I'm guessing this method can only work if the child column is in a different table then the parent. The old mutating table problem would probably raise it's ugly head.

Of course this is all speculation as I have not attempted to verify this.

-- 

Ed Bruce
<Bruce_at_ha.hac.com>
<ebruce_at_iquest.com>