Re: Need indexes on inherited tables?

you can find more information here: <a
href="http://www.postgresql.org/docs/7.4/static/sql-createtable.html">http://www.postgresql.org/docs/7.4/static/sql-createtable.html</a>
(underthe parameters section). For unique constraints, the only thing I can think of is a table check constraint
(SELECTcount(pk)=0).<br /><br /> I agree with you, right now there are many inconveniences with postgresql inheritance,
that'swhy I never use inheritance this way. <br /> I usually stick to the "standard" way, for example, I create a
peopletable, and for every people I want to insert in my database there is one row in this table, plus one row with the
sameid/pk (which is also a fk) in a specialized table (let's say students, teachers, whatever). <br /> This simplifies
things,because attribute definitions/defaults/constraints are always in one table. Maybe you get into some other
troubles,but I find them less painfull <img align="middle" alt=":)" border="0"
src="cid:1088440168.910.151.camel@taz.oficina"/><br /><br /> On Sat, 2004-06-26 at 19:30, Phil Endecott wrote:
<blockquotetype="CITE"><pre><font color="#737373"><i>I asked if derived tables use primary key indexes generated in the
basetables that they inherit from. 

Franco Bruno Borghesi replied:
> [the derived table] is not inheriting any indexes, neither the
> primary key constraint.

OK, thanks!  I had come to the conclusion that it was not using the index, but I'm really surprised to hear that the
primarykey constraint that I define in a base table is not inherited.  Are any constraints inherited?  What happens if
Ideclare a single-column primary key?  What if I declare a "not null" constraint or a "check" constraint in a base
table?

Having to replicate the constraints and indexes for each derived table is a pain - lots of error-prone typing - but
thereis a more serious problem: how can I ensure that these keys are unique across all of the derived tables?  (i.e. if
T1and T2 inherit from B, and B's primary key is (id,xx), then I want there to be at most one row in (T1 union T2) that
hasany value of (id,xx).) 

Is this a possible area for future enhancements?

Regards,

--Phil.

</i></font></pre></blockquote>