Thread: On the subject of inheritance
I have been looking at documentation on inheritance and am not overly
clear on all aspects of it. Fortunately, the examples in 'PostgreSQL'
from 'Developers Library' showed examples which were helpful however I
am still curious if it would work for my circumstance.
I have a table which is used throughout the site and tracks users.
This information is what is required by the scripts to establish
identity, authenticate and correspond with off site. e.g.
USER
UserName VARCHAR(10)
UserPass VARCHAR(12)
UserEmail VARCHAR(30)
There is a new aspect to the site which is completely optional and seems
like a good candidate for inheritance of the user table. Simply put,
these additional attributes ADDITIONAL would be in a separate table
applied to and used only when necessary. This brings me to my questions.
OPTIONAL (Inherits from USER)
OptionalFirstName VARCHAR(15)
OptionalLastName VARCHAR(15)
OptionalAddress VARCHAR(45)
OptionalPhone VARCHAR(15)
First of all, does a row created in the USER table appear in the
OPTIONAL table which inherits from it? Meaning could I add in the
optional attributes at a later time using a simply ALTER TABLE OPTIONAL
statement when the UserName exists in USER?
Secondly, if the above is not possible does this require me to recreate
the entry from USER into OPTIONAL? And if so, what exactly happens?
Are there two records now or just one? Would I get a referential
integrity error and what would happened if the entry in USER was removed
would it be ripped from OPTIONAL as well?
Any clarification on this would be appreciated!
Martin Foster
martin@ethereal-realms.org
Martin Foster <martin@ethereal-realms.org> writes:
> I have a table which is used throughout the site and tracks users.
> This information is what is required by the scripts to establish
> identity, authenticate and correspond with off site. e.g.
> USER
> UserName VARCHAR(10)
> UserPass VARCHAR(12)
> UserEmail VARCHAR(30)
> There is a new aspect to the site which is completely optional and seems
> like a good candidate for inheritance of the user table. Simply put,
> these additional attributes ADDITIONAL would be in a separate table
> applied to and used only when necessary. This brings me to my questions.
> OPTIONAL (Inherits from USER)
> OptionalFirstName VARCHAR(15)
> OptionalLastName VARCHAR(15)
> OptionalAddress VARCHAR(45)
> OptionalPhone VARCHAR(15)
> First of all, does a row created in the USER table appear in the
> OPTIONAL table which inherits from it? Meaning could I add in the
> optional attributes at a later time using a simply ALTER TABLE OPTIONAL
> statement when the UserName exists in USER?
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
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