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

Sun, 31 Dec 2000 15:42:00 +0100 (CET)
Ferruccio Zamuner <nonsolosoft@diff.org>
PRIMARY KEY and INHERITANCE
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?
  
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, and hate thine enemy. But I say unto 
      you, Love your enemies, bless them that curse you, do 
      good to them that hate you, and pray for them which 
      despitefully use you, and persecute you;"          
                                     Matthew 5:43,44 



Sun, 31 Dec 2000 19:13:35 +0000
"Oliver Elphick" <olly@lfix.co.uk>
Re: PRIMARY KEY and INHERITANCE 
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

Mon, 1 Jan 2001 10:09:52 +1100
Horst Herb <hherb@malleenet.net.au>
Re: PRIMARY KEY and INHERITANCE
   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 | fer
 table_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

Tue, 2 Jan 2001 01:34:55 +0100 (CET)
Ferruccio Zamuner <nonsolosoft@diff.org>
Re: PRIMARY KEY and INHERITANCE