Обсуждение: ...
I get a "referential integrity violation", but the referenced key *does*
exist in the referenced table.
ICS=#
ICS=# insert into item_attrib_a (id,value) values ('10000000014','yes');
ERROR: <unnamed> referential integrity violation - key referenced from item_attrib_a not found in object
ICS=# select * from object where id = '10000000014';
id
-------------
10000000014
(1 row)
ICS=# \d item_attrib_a
Table "item_attrib_a"
Attribute | Type | Modifier
---------------+--------------------------+-------------------------------------------
last_modified | timestamp with time zone | not null default "timestamp"('now'::text)
id | bigint |
value | text |
ICS=# \d object
Table "object"
Attribute | Type | Modifier
-----------+--------+--------------------------------------------------------------------------------------
-------------------------------------------------------------
id | bigint | not null default (float8(nextval('object_id_seq'::text)) + (float8(CASE WHEN (getpara
masint('user_location'::text) NOTNULL) THEN getparamasint('u
Index: object_id_key
I created the table "item_attrib_a" like so:
CREATE TABLE item_attrib_a (
id int8 references object (id) on delete cascade,
value TEXT
) inherits (last_modified);
I don't think that it's important, but the table "object" is inherited by
other tables.
I'd appreciate any suggestions.
Mike Adler
On Fri, 5 Apr 2002, Michael Adler wrote:
>
> I get a "referential integrity violation", but the referenced key *does*
> exist in the referenced table.
>
> ICS=#
> ICS=# insert into item_attrib_a (id,value) values ('10000000014','yes');
> ERROR: <unnamed> referential integrity violation - key referenced from item_attrib_a not found in object
> ICS=# select * from object where id = '10000000014';
> id
> -------------
> 10000000014
> (1 row)
[snipped]
> I created the table "item_attrib_a" like so:
>
> CREATE TABLE item_attrib_a (
> id int8 references object (id) on delete cascade,
> value TEXT
> ) inherits (last_modified);
>
> I don't think that it's important, but the table "object" is inherited by
> other tables.
In fact it may certainly be... References constraints do not inherit
to children currently. The constraint selects from only the named table
(do a select * from ONLY object where id=...) and I'd guess that the row
is actually in one of the children.
On Fri, 5 Apr 2002, Stephan Szabo wrote: > > I get a "referential integrity violation", but the referenced key *does* > > exist in the referenced table. > > > > I don't think that it's important, but the table "object" is inherited by > > other tables. > > In fact it may certainly be... References constraints do not inherit > to children currently. The constraint selects from only the named table > (do a select * from ONLY object where id=...) and I'd guess that the row > is actually in one of the children. I think the solution is to create a unique index on the child table so that you can reference the inherited column. In that "gee, it would be nice" category of suggestions, I'd like to see more documentation on inheritance, it's limitations and suggested workarounds. This has probably all been said before.. The interactive docs on inheritance suggest one workaround for inherited columns as foreign keys: use a "CHECK" constraint with a custom function instead of REFERENCES constraint. Without much investigation, it seems like a better idea to create a unique index on the child table and REFERENCE that. This seems simpler to setup and affords more integrity features. I haven't really tried it, though. The other limitation that I didn't find in the main docs is that child tables don't inherit triggers. You have to add a trigger for each child table, although I was able to reuse the same function. It would be great to have this info in one place in the docs. I feel like including the obvious: PostgreSQL is spectacular. I can't imagine using anything else in this problem space. Mike Adler
On Sat, 6 Apr 2002, Michael Adler wrote: > > On Fri, 5 Apr 2002, Stephan Szabo wrote: > > > > I get a "referential integrity violation", but the referenced key *does* > > > exist in the referenced table. > > > > > > I don't think that it's important, but the table "object" is inherited by > > > other tables. > > > > In fact it may certainly be... References constraints do not inherit > > to children currently. The constraint selects from only the named table > > (do a select * from ONLY object where id=...) and I'd guess that the row > > is actually in one of the children. > > I think the solution is to create a unique index on the child > table so that you can reference the inherited column. That only works if you only want to reference the child. If you want to reference a number of tables in the same hierarchy you're kind of out of luck since all of the references constraints need to be satisfied. One thing I've seen for that is to make another table that actually holds the ids that would otherwise be inherited and have the tables in the tree reference that one for their id (each table needs the constraint of course). Then all the other tables that want to reference the base can reference the id table instead. This is alot more work in some ways because now you have another table that you have to modify. Of course, since primary keys don't inherit either, it actually allows you to get a unique constraint across the ids as well... > In that "gee, it would be nice" category of suggestions, I'd like to see > more documentation on inheritance, it's limitations and suggested > workarounds. This has probably all been said before.. That's probably a good idea, but nobody's ever stepped up to do it, mostly because inheritance needs alot of work and everyone's hoping that someone will fix all these cases. > The interactive docs on inheritance suggest one workaround for inherited > columns as foreign keys: use a "CHECK" constraint with a custom function > instead of REFERENCES constraint. Without much investigation, it seems > like a better idea to create a unique index on the child table and > REFERENCE that. This seems simpler to setup and affords more integrity > features. I haven't really tried it, though. The check constraint is incomplete as well without triggers on each of the tables of the inheritance tree to prevent delete/update (or do referential actions), but it's the easiest way to get part of the implementation.
On Sat, Apr 06, 2002 at 10:27:37AM -0500, Michael Adler wrote: [ . . . ] > In that "gee, it would be nice" category of suggestions, I'd like to see > more documentation on inheritance, it's limitations and suggested > workarounds. This has probably all been said before.. Does anyone know where there might be a document on what exactly it does and what sort of scenario would benefit from this feature? The docs I've seen all describe how to use it, not what it really is. They presume that you already know that you want it. Regards, Frank
Frank Joerdens <frank@joerdens.de> writes: > Does anyone know where there might be a document on what exactly it does > and what sort of scenario would benefit from this feature? http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/tutorial-inheritance.html regards, tom lane
This is the a page in the interactive docs. There's a modest dialogue with workaround suggestions. http://www.postgresql.org/idocs/index.php?inherit.html On Mon, 8 Apr 2002, Tom Lane wrote: > Date: Mon, 08 Apr 2002 10:09:54 -0400 > From: Tom Lane <tgl@sss.pgh.pa.us> > To: Frank Joerdens <frank@joerdens.de> > Cc: Michael Adler <adler@glimpser.org>, > Stephan Szabo <sszabo@megazone23.bigpanda.com>, > pgsql-general@postgresql.org > Subject: Re: [GENERAL] inherited columns as foreign keys WAS "no subject" > > Frank Joerdens <frank@joerdens.de> writes: > > Does anyone know where there might be a document on what exactly it does > > and what sort of scenario would benefit from this feature? > > http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/tutorial-inheritance.html > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > Mike