Re: [HACKERS] Inheritance, referential integrity and other constraints

Chris Bitmead wrote: >Oliver Elphick wrote:
... >>   a (id char2 primary key, name text not null) >>   b (tp char(1) not null default 'B', supplier text) inherits
(a);>>   c (tp char(1) not null default 'C', customer text) inherits (a); >>  >> It seems quite a sensible use of
inheritanceto allow different defaults >> for tp in tables b and c.  However, we then have difficulty here: >>  >>   d
(c1text) inherits (b,c) >>  >> Which tp is to be inherited?  At present, PostgreSQL avoids the problem >> by not
inheritingany constraints.  We need something like: >>  >>   d (c1 text) inherits (b,c) using b.tp > >Hmmm. I don't
thinkthat's right at all. For example tp might be a >different type in b and c, and code might depend on that.
 

No, the inheritance system doesn't allow them to be different types.
You get an error if you try to create such a table:

junk=> \d d
Table    = d
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| id                               | char()                           |     1 |
| words                            | text                             |   var |
| nu                               | float8                           |     8 |
+----------------------------------+----------------------------------+-------+
junk=> \d b
Table    = b
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| id                               | char()                           |     1 |
| words                            | text                             |   var |
| nu                               | numeric                          |  8.2  |
+----------------------------------+----------------------------------+-------+
junk=> \d d
Table    = d
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| id                               | char()                           |     1 |
| words                            | text                             |   var |
| nu                               | float8                           |     8 |
+----------------------------------+----------------------------------+-------+
junk=> create table e (x text) inherits (b,d);
ERROR:  float8 and numeric conflict for nu

And this is right because `SELECT nu FROM b*' and `SELECT nu FROM d*'
both need to work.
 >                                                          It would be >logically unreasonable to have an inherited
"d"not have BOTH tp from b >and c.
 

Because the column names are identical, they are overlaid and treated
as the same column.  This is so whether or not they ultimately derive
from the same parent, so it isn't strictly a case of repeated inheritance
as in Eiffel. (There, repeatedly inherited features of the same parent
are silently combined, but identical names from unrelated classes are
conflicts.)
 >       I think from memory, Eiffel solves this by renaming doesn't it? I >think you need either renaming or scope
resolvingsyntax. This would >probably get very messy, and I think it's probably quite sufficient to >force the user to
notinherit the same name from b and C. If you want >that, you have to rename tp to be something else in b and/or c. 
 
But we do allow this at the moment; identically named and typed columns
are taken to be the same column.  This is so, even if they don't appear
in the same order:

junk=> \d m
Table    = m
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| c1                               | char()                           |     2 |
| c2                               | int4                             |     4 |
| c3                               | text                             |   var |
| c4                               | numeric                          |  8.2  |
+----------------------------------+----------------------------------+-------+

so it looks as if the recent discussion about column ordering and
inheritance was off the point!
 >> Final note: I have just realised that most of what I am using inheritance >> for could be done with views and
unions,provided that we can REFERENCE a >> view (which I haven't tested).  One really radical option would be to stri
 >p >> out inheritance altogether! > >Please no! Yep, inheritance in SELECT is actually implemented as a UNION
>internally.But don't dump it! 
 
Well no; I didn't really mean it!



-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver              PGP key from public servers; key
ID32B8FAA1                ========================================    "Come now, and let us reason together, saith the
LORD;     though your sins be as scarlet, they shall be as white     as snow; though they be red like crimson, they
shall     be as wool."                     Isaiah 1:18