Re: How to ensure data consistency?

"Felix E. Klee" <felix.klee_at_inka.de> wrote in message news:20040910023557.5438db54.felix.klee_at_inka.de...
> On Thu, 9 Sep 2004 01:46:28 +0200 Felix E. Klee wrote:
> > To avoid orphaned rows in subtables, it seems necessary to set up
> > circular foreign key constraints.

A viable alternative to this if PostgreSQL supports "instead of" triggers on views, is a set of views that the external world sees as "tables", each of which is composed of a join of the base relation representing the generalization and a single base relation representing a single specialization type. A trigger declared on this view would ensure the consistent population, update, or removal of both the generalized entity and the specialized entity with the application never being the wiser.

This does involve the writing of some code in the trigger, but it does ensure a consistent manipulation of the generalization/specialization scheme if applications use this mechanism as its interface.

>
> Just checked it, and it seems to work fine. To avoid an overload of new
> key values one could use the following model. Again checks that b is non
> null when spcialization_type="b", etc. need to be added. But this seems
> to be simple. The disadvantage of the model is of course that it
> contains at least one null value in each row in TS.
>
Nulls provide problems for me that are fundamental, but given the tools (and standard) we are given, they are sometimes difficult to avoid.
> TS
> t_id (PK)
> specialization_type: Either "a", "b", or "c".
> b_t_id (UNIQUE) (FK->t_id) (FK->BS.t_id)
> c_t_id (UNIQUE) (FK->t_id) (FK->CS.t_id)
> [...]
>
> BS
> t_id (PK) (FK->TS.t_id)
> [...]
>
> CS
> t_id (PK) (FK->TS.t_id)
> [...]

It seems like you've made a lot of progress. Thanks for posting the results of your efforts.

>
> Felix

Regards,

Dan Received on Fri Sep 10 2004 - 18:18:21 CEST