Re: Inheritance, Primary Keys and Foreign Keys - Mailing list pgsql-hackers

A Saturday 13 May 2006 08:33, Thomas Hallgren va escriure:
> Albert Cervera Areny wrote:
> > Of course, that's an option for my case. Just wanted to know if this
> > solution could be useful for PostgreSQL in general. Mainly because I'll
> > add some triggers to check what maybe PostgreSQL should do itself but
> > it's unimplemented.
> >
> > If that's not interesting or a proper solution for PostgreSQL I'll add it
> > using the existing DDL in my application and that's all.
> >
> > What do you think?
>
> I think that if you want the database to improve its current inheritance
> behavior, then this trigger set is too limited. You need triggers that
> maintain both unique and primary keys and triggers that maintain cascade
> behavior.

True. I think those triggers should be used for all unique indexes, not only 
primary keys. What do you mean with triggers that maintain cascade behavior?

>
> In order to make it really good, you would also need to add some
> functionality to the mechanisms that maintain references. Today, they don't
> recognize inheritance at all.

Indeed, foreign keys should be inherited, as well as unique keys. And to look 
for the reference they should SELECT FROM instead of SELECT FROM ONLY.


>
> Personally, I use Hibernate. It tries to compensate for the lack of these
> features but since it is a middle-tier (or client) solution, it's not
> ideal. Another client can still violate the rules and to maintain integrity
> in the client is negative from a performance standpoint. I think it would
> be great if PostgreSQL could provide a more complete set of features that
> would enable inheritance. A good start would be to extend it with the
> functionality needed to maintain references, cascade actions, and enforce
> unique constraints.
>
> On the other hand, inheritance is a tricky business and a good OO-RDB
> mapper will give you several choices of how it should be mapped. There's no
> "one size fits all". The best solution is probably if someone (you
> perhaps?) writes an external OO-RDB mapper module that executes in the
> backend. The author of such a tool would of course need some new nifty
> backend API's in order to do whats needed with references etc.
>
> I actually wrote something similar using Oracle a couple of years ago. It
> was based on type inheritance and views rather then tables and used
> 'instead of' actions on all views (Oracles own mechanisms where far to
> limited). In some respect, I think that is a better solution. Inheritance
> and all that comes with it is more a 'type' thing then a 'table' thing in
> my world. A view is then used to _map_ the types to persistent storage,
> i.e. the 'tables'.

The library I'm developing (http://kandau.berlios.de) aims for very easy 
object persistency, and it offers a default O-R mapping schema. If the user 
wants, she can write her own, but as I'm working with PostgreSQL, I wanted to 
use the inheritance mechanism and extend it to fit the needs of this 
application. I think that inheritance at the database level as it's 
implemented in PostgreSQL is a very smart solution and I'd like it to be the 
default for my application.

>
> Regards,
> Thomas Hallgren

Thanks for your comments