Thread: PRIMARY KEY and INHERITANCE
Hi, some months ago I've asked for a suggestion for this bug: create table a ( id serial primary key, something text ); create table b ( morething text ) inherits (a); create table c ( trouble int references b; ); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) ERROR: PRIMARY KEY for referenced table "b" not found How is possible to resolve this bug? How is possible to talk about a ORDBMS with this kind of error? I've looked for workaround, but: create table d ( mytext text, primary key (id) ) inherits (a); ERROR: CREATE TABLE: column 'id' named in key does not exist How can I help you to fix this? Best wishes, \fer
Ferruccio Zamuner wrote: >Hi, > >some months ago I've asked for a suggestion for this bug: > >create table a ( > idserial primary key, > something text >); > >create table b ( > morething text >) inherits (a); > >create tablec ( > trouble int references b; >); >NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check( >s) >ERROR: PRIMARY KEY for referenced table "b" not found > > >How is possible to resolve this bug? >How is possibleto talk about a ORDBMS with this kind of error? Inheritance badly needs fixing (= I very much want it to work right!). >I've looked for workaround, but: > >create table d ( > mytext text, > primary key (id) >) inherits (a); >ERROR: CREATE TABLE: column 'id' named in key does not exist This does work at 7.1 >How can I help you to fix this? > I had some suggestions for Inheritance semantics that no-one has yet commented on (24th December "Re: [HACKERS] RI problem with inherited table (fwd)"). If people agree with these, I would like to start research on how to implement them. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "Ye have heard that it hath been said, Thou shalt love thy neighbour, andhate thine enemy. But I say unto you, Love your enemies, bless them that curse you, do good to them that hateyou, and pray for them which despitefully use you, and persecute you;" Matthew 5:43,44
On Monday 01 January 2001 01:42, Ferruccio Zamuner wrote: > Hi, > > some months ago I've asked for a suggestion for this bug: > > create table a ( > id serial primary key, > something text > ); > > create table b ( > morething text > ) inherits (a); > > create table c ( > trouble int references b; > ); > NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY > check(s) ERROR: PRIMARY KEY for referenced table "b" not found > > > How is possible to resolve this bug? > How is possible to talk about a ORDBMS with this kind of error? It is not a bug, I would call it a missing feature. I had the same problem, and somebody from this list helped me with a private email. I think this should be included in the FAQ and general documentation. What happens is that the attribute "id" is inherited, but the index on "id" is not. The workaround is: create unique index table_b_id on b(id); Then the index exists, and the foreign key can be referenced. Yes, I share your believe that the automatic generation of this index should be a minimum requirement for an _O_RDBMS, but who will have time to implement it properly? Regards, Horst
From: Horst Herb <hherb@malleenet.net.au> Date: Mon, 1 Jan 2001 10:09:52 +1100 >> create table a ( >> id serial primary key, >> something text >> ); >> >> create table b ( >> morething text >> ) inherits (a); >> >> create table c ( >> trouble int references b; >> ); >> NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY >> check(s) ERROR: PRIMARY KEY for referenced table "b" not found > > >> How is possible to resolve this bug? >> How is possible to talk about a ORDBMS with this kind of error? > It is not a bug, I would call it a missing feature. I had the same problem, > and somebody from this list helped me with a private email. I think this > should be included in the FAQ and general documentation. > What happens is that the attribute "id" is inherited, but the index on "id" > is not. The workaround is: > create unique index table_b_id on b(id); > Then the index exists, and the foreign key can be referenced. Thank you Horst, I was very happy for this workaround: it could make me able to use classes during the design and it could give me a power approach, but now: \di List of relations Name | Type | Owner ---------------------------+-------+-------a_pkey | index | fertable_b_id | index | fer # create table c (test int references b); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) ERROR: PRIMARY KEY for referenced table "b" not found I've also tried to build b_pkey unique index:b_pkey | index | fer The trouble still persists. May someone give me another workaround for PostgreSQL 7.0.3? Thank you in advance, \fer