Thread: Primary key inheritance problem
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
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.
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
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>
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