[BUGS] references fail over rows inserted via inherited tables

[pgsql-bugs]

Diego Saravia ([EMAIL PROTECTED]) 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:   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


---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

Re: [BUGS] references fail over rows inserted via inherited tables

[Stephan Szabo]

On Sun, 20 May 2001 [EMAIL PROTECTED] wrote:

> Diego Saravia ([EMAIL PROTECTED]) 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.


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])