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