Re: foreign key problems

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.