Primary key inheritance problem

[Bruno Baguette]

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 - pg******@baguette.net

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Stephan Szabo]

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.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Oliver Elphick]

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 ol**@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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Matthew M Davis]

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
<mm*@teledavis.com>

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Oliver Elphick]

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 ol**@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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html