From: BARTKO, Zoltán <bartko.zoltan@pobox.sk>
To: pgsql-sql@postgresql.org
Cc: sszabo@megazone.bigpanda.com
Subject: Re: foreign key problems
Date: Wed, 5 Jan 2005 10:30:03 +0100
Message-ID: <200501050930.j059U27s019437@www7.pobox.sk> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bartko.zoltan@pobox.sk, sszabo@megazone.bigpanda.com
Subject: Re: foreign key problems
In-Reply-To: <200501050930.j059U27s019437@www7.pobox.sk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox