Re: Object Relational, Foreign Keys and Triggers

On Mon, 24 Jan 2005, Alex Turner wrote:

> Insert fails with a foreign key constraint error because entity_phone
> points to entity, not person, and the rows aren't physicaly in entity,
> they are in person.
>
> Two questions:
> 1) Why can't we make this work the 'right' way - not doing so either
> breaks OO or brakes RDBMS. 1)a) Whats the point of an RDBMS if you
> can't specify foreign keys that work because you choose to use OO
> features (I somewhat appreciate that there is a trigger inheritance
> problem, can't we just define the rules and order of precident and
> solve it)?

There are multiple problems involved mostly due to the fact that
inheritance really need alot of work. For example, the actual
implementation of the schema you gave has no interlock to prevent
duplicate rows in person and entity (or another entity subclass). The
primary key implementation also only guarantees local uniqueness.
Inheritance really needs some developers who care strongly about it.

> 2) Whats the best way to manage this with triggers. Obviously one can
> create a trigger on entity and on person for delete so that it removes
> corresponding rows in entity_phone. But whats the best way to create
> a trigger that ensures that entity_ids that are used in entity_phone
> exist in entity and it's subtables thats fast. You could do:

There's been discussion about this in the past, so you can get details
from the archives, but using a separate table to store the ids with
triggers between entity and person and the new table which manage the id
list has been proposed as a workaround.