Re: Re: [GENERAL] child table doesn't inherit PRIMARY KEY? - Mailing list pgsql-hackers

From Bruce Momjian
Subject Re: Re: [GENERAL] child table doesn't inherit PRIMARY KEY?
Date
Msg-id 200101242356.SAA18280@candle.pha.pa.us
Whole thread Raw
In response to Re: Re: [GENERAL] child table doesn't inherit PRIMARY KEY?  ("Oliver Elphick" <olly@lfix.co.uk>)
List pgsql-hackers
Thanks.  Done.

> Bruce Momjian wrote:
>   >> On Wed, 24 Jan 2001, Bruce Momjian wrote:
> 
>   >I smell TODO item.  In fact, I now see a TODO item:
>   >
>   >* Unique index on base column not honored on inserts from inherited table
>   >  INSERT INTO inherit_table (unique_index_col) VALUES (dup) should fail
>   >  [inherit]
>   >
>   >So it seems the fact the UNIQUE doesn't apply to the new table is just a
>   >manifestion of the fact that people expect UNIQUE to span the entire
>   >inheritance tree.  I will add the emails to [inherit] and mark it as
>   >resolved.
> 
> Bruce, could you add this text to TODO.detail on the subject of 
> inherited constraints.  I first sent it on Christmas Eve, and I 
> think most people were too busy holidaying to comment.
> 
> =================================================================
> Tom Lane wrote:
>   >Hm.  The short-term answer seems to be to modify the queries generated
>   >by the RI triggers to say "ONLY foo".  I am not sure whether we
>   >understand the semantics involved in allowing a REFERENCES target to be
>   >taken as an inheritance tree rather than just one table, but certainly
>   >the current implementation won't handle that correctly.
> 
> May I propose these semantics as a basis for future development:
> 
> 1. An inheritance hierarchy (starting at any point in a tree) should be
> equivalent to an updatable view of all the tables at the point of
> reference and below.  By default, all descendant tables are combined
> with the ancestor for all purposes.  The keyword ONLY must be used to
> alter this behaviour.  Only inherited columns of descendant tables are
> visible from higher in the tree.  Columns may not be dropped in descendants.
> If columns are added to ancestors, they must be inserted correctly in
> descendants so as to preserve column ordering and inheritance.  If
> a column is dropped in an ancestor, it is dropped in all descendants.
> 
> 2. Insertion into a hierarchy means insertion into the table named in
> the INSERT statement; updating or deletion affects whichever table(s)
> the affected rows are found in.  Updating cannot move a row from one
> table to another.
> 
> 3. Inheritance of a table implies inheriting all its constraints unless
> ONLY is used or the constraints are subsequently dropped; again, dropping
> operates through all descendant tables.  A primary key, foreign key or
> unique constraint cannot be dropped or modified for a descendant.  A
> unique index on a column is shared by all tables below the table for
> which it is declared.  It cannot be dropped for any descendant.
> 
> In other words, only NOT NULL and CHECK constraints can be dropped in
> descendants.
> 
> In multiple inheritance, a column may inherit multiple unique indices
> from its several ancestors.  All inherited constraints must be satisfied
> together (though check constraints may be dropped).
> 
> 4. RI to a table implies the inclusion of all its descendants in the
> check.  Since a referenced column may be uniquely indexed further up
> the hierarchy than in the table named, the check must ensure that
> the referenced value occurs in the right segment of the hierarchy.  RI
> to one particular level of the hierarchy, excluding descendants, requires
> the use of ONLY in the constraint.
> 
> 5. Dropping a table implies dropping all its descendants.
> 
> 6. Changes of permissions on a table propagate to all its descendants.
> Permissions on descendants may be looser than those on ancestors; they
> may not be more restrictive.
> 
> 
> This scheme is a lot more restrictive than C++'s or Eiffel's definition
> of inheritance, but it seems to me to make the concept truly useful,
> without introducing excessive complexity.
> 
> ============================================================
> 
> -- 
> Oliver Elphick                                Oliver.Elphick@lfix.co.uk
> Isle of Wight                              http://www.lfix.co.uk/oliver
> PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47  6B 7E 39 CC 56 E4 C1 47
> GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
>                  ========================================
>      "If anyone has material possessions and sees his
>       brother in need but has no pity on him, how can the
>       love of God be in him?"
>                                     I John 3:17 
> 
> 
> 


--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


pgsql-hackers by date:

Previous
From: Bruce Momjian
Date:
Subject: Re: Re: unixODBC again :-(
Next
From: Bruce Momjian
Date:
Subject: Re: FW: Postgresql on win32