Thread

1. references fail over rows inserted via inherited tables

   PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2001-05-20T23:38:31Z

   Diego Saravia (dsa@unsa.edu.ar) reports a bug with a severity of 4
   The lower the number the more severe it is.
   
   Short Description
   references fail over rows inserted via inherited tables
   
   Long Description
   When you insert a row via an inherited table, with 7.1.1, you can see in the parent table.
   
   But when you try to insert a reference to that value you can't.
   
   Sample Code
   /*I have a problem with inherited primary keys 
   
   When I referenced a primary key inserted via an inherited table
   it gives you an error
   
   Is this the right way?
   
   Example
   *************************************************
   */
   
   
   select version();  
   /*                            version
   ---------------------------------------------------------------
    PostgreSQL 7.1.1 on i686-pc-linux-gnu, compiled by GCC 2.95.2
   (1 row)                                    
   */
   
   create table persona(
    id serial primary key,
    nombre text
   );
    
   create table usuario(      
    alias text
   ) inherits (persona) ;
   
   
   insert into usuario (alias,nombre) values ('no','NO');
   
   insert into persona (nombre) values ('SI'); 
   
   select * from persona;       
   
   /*
    id | nombre
   ----+--------
     2 | SI
     1 | NO
   (2 rows)          
   */
   
   select * from usuario;    
    
   /*
    id | nombre | alias
   ----+--------+-------
     1 | NO     | no
   (1 row)                   
   */
    
   create table trabajo(
    titulo text, 
    propietario integer references persona                                       );           
   
   
   insert into trabajo (titulo,propietario) values ('capaz',1); 
   
   
   /*ERROR:  <unnamed> referential integrity violation - key referenced from trabajo not found in persona   */   
   
   
   insert into trabajo (titulo,propietario) values ('capaz',2); 
   
   select * from trabajo;
   
   /*
    titulo | propietario
   --------+-------------
    capaz  |           2
   (1 row)                     
   */
   
   
   
   
   create table trabajo2(
    titulo text, 
    propietario integer references usuario                                       );           
   
   /*
   ERROR:  PRIMARY KEY for referenced table "usuario" not found     
   
   I think that this is also not Ok 
   
   */
   
      
   
   
   
   
   
   
   
   No file was uploaded with this report
   
   
   

2. Re: references fail over rows inserted via inherited tables

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2001-05-21T00:06:32Z

   On Sun, 20 May 2001 pgsql-bugs@postgresql.org wrote:
   
   > Diego Saravia (dsa@unsa.edu.ar) reports a bug with a severity of 4
   > The lower the number the more severe it is.
   > 
   > Short Description
   > references fail over rows inserted via inherited tables
   > 
   > Long Description
   > When you insert a row via an inherited table, with 7.1.1, you can see in the parent table.
   > 
   > But when you try to insert a reference to that value you can't.
   
   Yes.  Right now the constraint only references the rows actually
   existing in the parent table not the hierarchy.  (See archives
   of -bugs and -general for alot of message about this).
   
   > 
   > create table trabajo2(
   >  titulo text, 
   >  propietario integer references usuario                                       );           
   > 
   > /*
   > ERROR:  PRIMARY KEY for referenced table "usuario" not found     
   > 
   > I think that this is also not Ok 
   > 
   > */
   
   This is because primary keys do not currently inherit to child
   tables, so there really isn't a primary key on usuario.