Re: foreign key problems

Ok, so I made some changes (manual "inheritance" of PK and FK
constraints), but nevertheless I get still the same dumb error. I made
a dump of the DB via pg_dump, it is available at

http://de.geocities.com/bartkozo/dump.tgz

DRecord was dropped, the columns moved into DObject. I still have no
idea why foreign keys work on other tables and do not on this one.

I have three users, superaspiramus, aspiramus and aspiramusadmin. The
first is the owner of the db.

After loading, the contents of the DObject table are:

aspiramus=> select * from DObject;
who | insteadof | privilege | createdat | objectid |
revision
| aname | description | objecttype | status
-----+-----------+-----------+----------------------------+----------+----------
+-------+-------------+------------+--------
-2 | | -1 | 2005-01-05 09:38:11.906+01 | 34 |
1
| a | a | 1 | 67
-2 | | -1 | 2005-01-05 09:37:27.625+01 | 35 |
0
| b | b | 1 | 61
-2 | | -2 | 2005-01-05 09:37:27.625+01 | 33 |
0
| name | description | 13 | 301
(3 riadkov)

when trying this:

aspiramus=> insert into AAttachment (createdby, privilege, insteadof,
objectid, bodyid) values (-2,-2,null,33,33);

I get:

ERROR: insert or update on table "aattachment" violates foreign key
constraint "aattachment_objectid_fkey"
DETAIL: Key (objectid)=(33) is not present in table "dobject".

I think the problem is not in front of the computer, but maybe I am
wrong. If so, I would appreciate some help as to where I am wrong.

I can bypass this problem by dropping the FK constraints from objectID
and bodyID in AAttachment, but it is not too kosher.

Thanks for your assistance

Zoltan

>
> On Tue, 4 Jan 2005, [iso-8859-2] BARTKO, Zoltán wrote:
>
> > 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).
>
> It looks like the set of table definitions you gave in the attached file
> do not actually work as given, and there's no example data. Running on
> 8.0 beta3 I was able to insert data that allowed me to place a row into
> AAttachment (and in fact I actually can insert some data that probably
> should not be allowed). I'm not sure if that's based on my basically
null
> definition of DRecord or not however.
>
> In general, however, foreign keys and inheritance don't mix (neither do
> primary keys or unique constraints). In general, they'll only currently
> work for the table they're in specifically and only for the table
> mentioned specifically. So, for example, the references constraints in
> AObjAssociation are not inherited by AAttachment. Inheritance really
needs
> someone to champion getting all of these deficiencies fixed.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>

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