Re: Inherited tables, indexes and serials.

Tuesday, October 17, 2000, 1:53:56 PM, DaVinci wrote:
D> When I create a table that inherits a serial field from another, the unique
D> index is created and used for parent but not for child. OTOH, serial can be
D> used by both tables. So, child desn't check by default for unique values.

D> My problem with that is conceptual. Is it normal? Do I must create index for
D> child? Where is then transparency of object orientedness?.


Yeah, I had this problem and I find it a little serious limit to
OO functionality. I hope the great PG will overcome this limit in
a future version, I hope it's doesn't lead to unpleasant
and hard-to-work side effects.
I don't know if it is really important to others, but I think that the
importance of RI-constraints and of features in
*tree-structured* data is not to be undervaluated.

Best regards
Nico


===========================
The World Is Flat? Wrong! ;)


On Friday, October 13, 2000, 6:36:25 PM, Stephan Szabo wrote:

SS> RI constraints do not follow inheritance trees at this time.
SS> The constraint is looking for rows in objects (not objects*) for
SS> the matching pk value.  There really isn't a work around I can think
SS> of at this time.

SS> Stephan Szabo
SS> sszabo@bigpanda.com

SS> On Fri, 13 Oct 2000, Nico D wrote:

>> Please let me know what I've missed about the referential
>> integrity ?
>> (using PG v.7.0.2)
>>
>>
>> test=> create table objects (id serial);
>> NOTICE:  CREATE TABLE will create implicit sequence 'objects_id_seq' for SERIAL column 'objects.id'
>> NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'objects_id_key' for table 'objects'
>> CREATE
>>
>> test=> create table books (title name) inherits(objects);
>> CREATE
>>
>> test=> create table opinions (idobject int4 REFERENCES objects(id) ON UPDATE CASCADE, comment text);
>> NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
>> NOTICE:  _outNode: don't know how to print type 726
>> CREATE
>>
>> test=> insert into books (title) values ('DataBases');
>> INSERT 691707 1
>> test=> select * from objects*;
>>  id
>> ----
>>   1
>> (1 row)
>>
>> test=> select * from books;
>>  id |   title
>> ----+-----------
>>   1 | DataBases
>> (1 row)
>>
>> test=> insert into opinions (idobject, comment) values (1, 'Very Wise');
>> ERROR:  <unnamed> referential integrity violation - key referenced from opinions not found in objects
>>
>> test=>
>>
>>