From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: BARTKO, Zoltán <bartko.zoltan@pobox.sk>
Cc: pgsql-sql@postgresql.org
Subject: Re: foreign key problems
Date: Wed, 5 Jan 2005 05:32:54 -0800 (PST)
Message-ID: <20050105052940.D60967@megazone.bigpanda.com> (raw)
In-Reply-To: <20050105052001.T60967@megazone.bigpanda.com>
References: <200501050930.j059U27s019437@www7.pobox.sk>
<20050105052001.T60967@megazone.bigpanda.com>
On Wed, 5 Jan 2005, Stephan Szabo wrote:
>
> On Wed, 5 Jan 2005, [iso-8859-2] BARTKO, Zoltán wrote:
>
> > 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;
>
> Do select * from ONLY DObject.
>
> The constraint currently only goes to DObject, records that are actually
> in DObject and not any tables derived from it. This is one of the
> deficiencies I was trying to refer to in the previous message. As a note,
> your primary keys will also not work to prevent duplicates of objectid
> between subclasses of DObject currently, and I'm not sure whether that
> matters to you:
>
> insert into dmessage (who, privilege, objectid, objecttype, status,
> aname,sender,receiver) values (-2,-2,33,13,301,'name',-2,-2);
>
> select objectid from dobject;
> objectid
> ----------
> 34
> 35
> 33
> 33
> (4 rows)
>
> Inheritance needs alot of work. :(
I forgot to mention that this has come up in the past, and some people
have sent messages about workarounds. I believe one involved pulling the
canonical key values out into a separate table that is managed by triggers
with foreign keys between each of the tables in the hierarchy to the key
table. Thus, a unique constraint on that key table would effectively span
all tables with such triggers and foreign keys to that table might work.
However, that doesn't work if you also have foreign keys to a derived
table from which other tables might be inherited.
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: sszabo@megazone.bigpanda.com, bartko.zoltan@pobox.sk
Subject: Re: foreign key problems
In-Reply-To: <20050105052940.D60967@megazone.bigpanda.com>
* 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