Re: Problem with referential integrity and inherited tables in 7.1.1

On Mon, 14 May 2001, Stefan Loidl wrote:

>
> Hi,
>
> the following (simplified) example works with 7.0.3,
> but fails with 7.1.1:
>
>
> CREATE TABLE ViewElement (
>     id                  serial
> );
>
> CREATE TABLE ViewNode (
>     nodeType     char
> ) INHERITS (ViewElement);
>
> CREATE TABLE ViewLink (
>     linkType  char
> ) INHERITS (ViewElement);
>
>
> CREATE TABLE ConnectedViewNodes (
>     linkId  int4 NOT NULL CONSTRAINT A REFERENCES ViewLink (id),
>     nodeId  int4 NOT NULL CONSTRAINT B REFERENCES ViewNode (id)
> );
>
>
> After the last create statement I get the following error:
> ERROR:  UNIQUE constraint matching given keys for referenced table
> "viewlink" not found

Which is true, because you need a unique constraint on the columns
to reference (which won't be inherited from ViewElement in any
case right now).  Put a UNIQUE(id) table constraint in ViewNode
and ViewLink and that should solve it.

> If I reference the ViewElement table instead of the inherited tables,
> I can create the ConnectedViewNodes table, but I can't insert into
> ConnectedViewNodes:
>
> CREATE TABLE ConnectedViewNodes (
>     linkId  int4 NOT NULL CONSTRAINT A REFERENCES ViewElement (id),
>     nodeId  int4 NOT NULL CONSTRAINT B REFERENCES ViewElement (id)
> );
>
> INSERT INTO ViewNode (nodeType) VALUES ('a');
> INSERT INTO ViewLink (linkType) VALUES ('b');
>
> INSERT INTO ConnectedViewNodes VALUES (2, 1);
>
> Here I get the following error after the last insert:
> ERROR:  b referential integrity violation - key referenced from
> connectedviewnodes not found in viewelement
>
> Is this a bug in 7.1.1 or is there an other way to do this?
> (Both ways work with 7.0.3)
No, actually 7.1 fixes the bug in 7.0 that allowed you to reference
non-unique keys because it didn't really actually work right.  It'd
*look* like it would work, but the moment you'd try to delete or
update stuff that was being referenced there was the chance it would
stop you from doing something that wouldn't violate the constraint
or allow you to violate the constraint.