Re: On the subject of inheritance

Tom Lane wrote:
>
> I think what you really want is just to add these columns to USER and
> allow them to be NULL when you don't have values for them.  They won't
> take up any material amount of space when they are NULL, so you need not
> worry about that.
>
> The problem with trying to do this with inheritance is that a row in
> OPTIONAL is not some sort of implicit extension of a matching row in
> USER, it is a separate full-fledged entity.  Thus, you'd be dealing with
> having to actually move rows from USER to OPTIONAL or vice versa
> depending on whether you had these values for that user or not.  That
> seems like useless complication of your application logic.
>
> It may help you to explain that an inherited table is physically
> completely separate from the parent, and contains its own complete rows
> including all the columns derived from the parent as well as any locally
> added columns.  The only "magic" is that a query that scans the parent
> table is automatically modified by the system to scan the child table(s)
> as well, so that the results include both parent and child rows.  Of
> course, the query can't refer to any non-inherited columns of the child,
> since it has no way to name them.
>
>> Would I get a referential integrity error
>
> You would not, because there isn't any reference from OPTIONAL to USER.
> Ideally, what you'd get if you put the same UserName into both tables
> is a unique-key violation.  At the moment, we don't have any way of
> enforcing uniqueness across multiple physical tables, and so the result
> would just be wrong :-(.  This is what the manual is talking about when
> it says that inheritance doesn't currently play nicely with unique (or
> foreign-key) constraints.
>
>             regards, tom lane

That is pretty much what I thought actually.    So there are no real
surprises there.    Though the documentation could use some examples
pulling data from the inherited rows!

Thanks.    Pretty much exactly what I liked.

    Martin Foster
    martin@ethereal-realms.org