Thread

1. PRIMARY KEY and INHERITANCE

   Ferruccio Zamuner <nonsolosoft@diff.org> — 2000-12-31T14:42:00Z

   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
   
   

2. Re: PRIMARY KEY and INHERITANCE

   Horst Herb <hherb@malleenet.net.au> — 2000-12-31T23:09:52Z

   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
   
   

3. Re: PRIMARY KEY and INHERITANCE

   Ferruccio Zamuner <nonsolosoft@diff.org> — 2001-01-02T00:34:55Z

      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