Thread: Primary key inheritance problem

Primary key inheritance problem

From
Bruno Baguette
Date:
Hello,

I've a strange problem with inheritance on PostgreSQL 7.4.1. It seems
like tables don't inherits the primary key of the 'mother' table.
Here's a little sample to show my problem :

CREATE TABLE foo_a
(
        pk_idmyfoo INT2 PRIMARY KEY NOT NULL,
        myfoo_name VARCHAR(10)
);

CREATE TABLE child_foo
(
        child_foo_nickname VARCHAR(20)
) INHERITS (foo_a);

CREATE TABLE childfoo_bookmarks
(
        fk_idchildfoo INT2 NOT NULL REFERENCES child_foo (pk_idmyfoo),
        url VARCHAR(250)
);

The error I get is :
ERROR:  there is no unique constraint matching given keys for referenced
table "child_foo"

What can I do to solve that problem ?

Thanks in advance for your help !

--
Bruno Baguette - pgsql-ml@baguette.net

Re: Primary key inheritance problem

From
Stephan Szabo
Date:
On Fri, 27 Aug 2004, Bruno Baguette wrote:

> Hello,
>
> I've a strange problem with inheritance on PostgreSQL 7.4.1. It seems
> like tables don't inherits the primary key of the 'mother' table.
> Here's a little sample to show my problem :
>
> CREATE TABLE foo_a
> (
>         pk_idmyfoo INT2 PRIMARY KEY NOT NULL,
>         myfoo_name VARCHAR(10)
> );
>
> CREATE TABLE child_foo
> (
>         child_foo_nickname VARCHAR(20)
> ) INHERITS (foo_a);
>
> CREATE TABLE childfoo_bookmarks
> (
>         fk_idchildfoo INT2 NOT NULL REFERENCES child_foo (pk_idmyfoo),
>         url VARCHAR(250)
> );
>
> The error I get is :
> ERROR:  there is no unique constraint matching given keys for referenced
> table "child_foo"
>
> What can I do to solve that problem ?

Well, to shut up the message you can add a constraint to child_foo's
version of pk_idmyfoo.  However, as a note, that'll not guarantee
uniqueness between foo_a's pk_idmyfoo values and child_foo's ones.


Re: Primary key inheritance problem

From
Oliver Elphick
Date:
On Fri, 2004-08-27 at 11:58, Bruno Baguette wrote:
> Hello,
>
> I've a strange problem with inheritance on PostgreSQL 7.4.1. It seems
> like tables don't inherits the primary key of the 'mother' table.
...
> The error I get is :
> ERROR:  there is no unique constraint matching given keys for referenced
> table "child_foo"

Primary and foreign key constraints are not inherited.  This is a defect
in the current system.

> What can I do to solve that problem ?

Create an index table to provide a unique key for the whole hierarchy.
Each member of the hierarchy has a foreign key reference to it na has
its own primary key on the referencing field.  Use triggers to update
the index table.

--
Oliver Elphick                                          olly@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
     "Preach the word; be instant in season, out of season;
      reprove, rebuke, exhort with all longsuffering and
      doctrine."          II Timothy 4:2


Re: Primary key inheritance problem

From
"Matthew M Davis"
Date:
On Sunday 29 August at 12:34pm, Oliver Elphick had this to say:

> Primary and foreign key constraints are not inherited.  This is a defect
> in the current system.

It almost seems like a FEATURE to me, since it creates a different and
easy way to create one-to-many relationships.  But I didn't use it,
since the documentation says this is a Bad Thing that may be fixed
some day.  If the child table inherits the primary key constraint from
the parent, why not just put it in the same table?

--

Matthew M Davis
<mmd@teledavis.com>

Re: Primary key inheritance problem

From
Oliver Elphick
Date:
On Sun, 2004-08-29 at 18:07, Matthew M Davis wrote:
> On Sunday 29 August at 12:34pm, Oliver Elphick had this to say:
>
> > Primary and foreign key constraints are not inherited.  This is a defect
> > in the current system.
>
> It almost seems like a FEATURE to me, since it creates a different and
> easy way to create one-to-many relationships.  But I didn't use it,
> since the documentation says this is a Bad Thing that may be fixed
> some day.  If the child table inherits the primary key constraint from
> the parent, why not just put it in the same table?

If you understand inheritance from the point of view of object-oriented
programming, you want to be able to have a hierarchy where the top level
has characteristics which are extended further down.

So all mammals are animals and all dogs are mammals, and all three
groups share common characteristics which are properties of the parent
class.  But mammals have characteristics which are not applicable to all
animals and so are not appropriate to the parent class; similarly, dogs
have properties which are not common to all mammals.

Now one particular animal is either a mammal or it is not, and if it is,
it is either a dog or it is not.  What it is governs which class it
belongs to in the hierarchy.  Nevertheless, I may want to know about the
characteristics of animals without enquiring into the particular
characteristics of mammals; in that case an enquiry on the top-level of
the hierarchy is appropriate and will gather all information about all
animals.  If I want to make a further enquiry about properties peculiar
to mammals, it is appropriate to start my enquiry at that level in the
hierarchy.  However a particular animal can only be in one particular
class, therefore it would be preferable for the primary key to extend
over the whole hierarchy.

--
Oliver Elphick                                          olly@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
     "Preach the word; be instant in season, out of season;
      reprove, rebuke, exhort with all longsuffering and
      doctrine."          II Timothy 4:2