foreign key problems - Mailing list pgsql-sql

Dear all,

I have a problem:

if I create the tables that are in the attached file, I can't insert
rows into the AAttachment table, even though the rows in DObject with
the given primary key exist (PgSQL 8.0 rc1 complains about
(ownerid)=(insert the number here) not available in DObject. The same
happens with attribute bodyid).

I have noticed that this behavior is different, if I do not use
inherited tables, e.g. if I use

create table A (
a integer references B(b),
b integer references B(b));

create table B
(b serial primary key);

insert into B values (1);
insert into B values (2);
insert into A (a,b) values (1,2);

works flawlessly.

Is this a feature or a bug or is the problem in front of the computer?

thanks

Zoltan



____________________________________
RAMMSTEIN, 22.02.2005 o 20,00, Bratislava Incheba,
Info: 0904 666 363, http://www.xl.sk

create table AAssociation (createdBy    integer,                    --* the creator of the associationcreation
timestampwith time zone default 'now'        --* creation timestamp with time zone 
);

create table AObjAssociation (privilege    integer not null references SysPrivilege(privilege),        --* the
privilegeused to create the associationinsteadOf    integer references DPerson(person)        --* instead of whom is
theaction taken 
) inherits (AAssociation);

create table AAttachment (ownerID    bigint references DObject(object),    --* owner objectbodyID    bigint references
DObject(object)   --* attached object 
) inherits (AObjAssociation);

create table DPerson (person        serial primary key,    --* person row id#login        varchar(16) not null unique,
 --* login namepasswd        varchar(16) not null,    --* passwordaname        varchar not null,    --* name surname
   varchar not null,    --* surnameisAdmin        boolean default false,    --* is the person an admin?unique (aname,
surname)
) inherits (DOrgEntity);

create table DOrgEntity (who        integer not null,            --* record authorcreatedAt    timestamp with time zone
default'now',    --* creation timestamp with time zoneisActive    boolean default true            --* is the entity
active?
);

create table SysObjTypes (objectType    integer unique not null primary key,    --* object typeaname        text,
            --* nameisAttachment    boolean,                --* is attachment of other objectshasAttachment    boolean
                 --* has attachments 
);

create table SysStatus (status        integer unique not null primary key,    --* id#addAttachment    boolean,
     --* allow adding new attachmentsdropAttachment    boolean,                --* allow dropping existing
attachmentsaname       text not null,                --* status namedescription    text                    --* status
description
);

create table DObject (object        bigserial primary key,        --* id#revision    integer not null default 0,    --*
ordinalnumber of the commited changeaname        text not null,            --* object namedescription    text default
'',       --* object descriptionobjectType    integer not null references SysObjTypes(objectType),
 --* the object typestatus        integer not null references SysStatus(status),                        --* the object
statusunique(aname, description, objectType) 
) inherits (DRecord);

create table SysPrivilege (privilege    integer unique not null primary key,    --* id#aname        text not null,
         --* privilege namedescription    text                     --* privilege description 
);