Re: foreign key problems - Mailing list pgsql-sql

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