From: | BARTKO, Zoltán <bartko(dot)zoltan(at)pobox(dot)sk> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Cc: | sszabo(at)megazone(dot)bigpanda(dot)com |
Subject: | Re: foreign key problems |
Date: | 2005-01-05 09:30:03 |
Message-ID: | 200501050930.j059U27s019437@www7.pobox.sk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | CoL | 2005-01-05 12:50:42 | Re: Calling a table residing in another database from Plpgsql |
Previous Message | Kenneth Gonsalves | 2005-01-05 05:38:28 | Re: Calling a table residing in another database from Plpgsql |