Date: Wed, 5 Jan 2005 10:30:03 +0100
Message-Id: <200501050930.j059U27s019437@www7.pobox.sk>
From: "=?iso-8859-2?Q?BARTKO,_Zolt=E1n?=" <bartko.zoltan@pobox.sk>
Reply-To: "=?iso-8859-2?Q?BARTKO,_Zolt=E1n?=" <bartko.zoltan@pobox.sk>
Subject: Re: foreign key problems
To: pgsql-sql@postgresql.org
Cc: sszabo@megazone.bigpanda.com
MIME-Version: 1.0
Content-Type: TEXT/plain; charset=iso-8859-2
Content-Transfer-Encoding: QUOTED-PRINTABLE

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=20

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=3D> 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=3D> 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)=3D(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

>=20
> On Tue, 4 Jan 2005, [iso-8859-2] BARTKO, Zolt=E1n wrote:
>=20
> > 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)=3D(insert the number here) not available in DObject. The same
> > happens with attribute bodyid).
>=20
> 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.
>=20
> 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.
>=20
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>=20
>                http://www.postgresql.org/docs/faqs/FAQ.html
>=20
=0A____________________________________=0ARAMMSTEIN, 22.02.2005 o 20,00, Br=
atislava Incheba, =0AInfo: 0904 666 363, http://www.xl.sk =0A