Hello,
I'm using inheritance in my DB with PostgreSQL 7.3.4, and a friend of my
told me that there is a bug in that type of table.
To resume, the problem is:
- a table A have a primary key
- a table B references the primary key of A
- a table C inherits the A fields
- when I insert a tuple in table B that references something inserted at
A, everything is OK.
- when I insert a tuple in table B that references something inserted at
C, a integrity violation occurs.
To me, this looks not like a bug, because the tuple is not at A phisically.
Taking the example of a VIEW: its not possible to reference a column in a
view because the data is not phisically in it.
Here, i put an example where i try to show the "problem" (it is in
portuguese, but I think the names don't seem to be a problem for all):
> PostgreSQL 7.3.4
>
> everton=# create table pessoa (id_pessoa serial NOT NULL PRIMARY KEY,
> nome character varying(30));
>
> everton=# create table pessoafisica(cpf integer) INHERITS(pessoa);
>
> everton=# create table pessoajuridica(cnpj integer) INHERITS(pessoa);
>
> everton=# create table telefone(id_pessoa integer, telefone integer,
> foreign key (id_pessoa) references pessoa (id_pessoa));
>
> everton=# insert into pessoa(nome) values ('Everton');
>
> everton=# insert into pessoafisica(nome, cpf) values ('Alencar', 123);
>
> everton=# insert into pessoajuridica(nome, cnpj) values ('Philler', 999);
>
> everton=# select * from pessoafisica;
> id_pessoa | nome | cpf
> -----------+---------+-----
> 3 | Alencar | 123
>
> everton=# select * from pessoajuridica;
> id_pessoa | nome | cnpj
> -----------+---------+------
> 4 | Philler | 999
>
> everton=# select * from pessoa;
> id_pessoa | nome
> -----------+---------
> 2 | Everton
> 3 | Alencar
> 4 | Philler
>
> everton=# insert into telefone(id_pessoa, telefone) values (2, 5410000);
> INSERT 22234 1
> everton=# insert into telefone(id_pessoa, telefone) values (4, 5413443);
> ERROR: $1 referential integrity violation - key referenced from
> telefone not found in pessoa
>
Like I said, this is not a problem for me, but I want to know if someone
thinks this can be considered a real problem, or if something will be done
to try to correct (or modify) this operation.
Thanks to all,
Marcelo Soares
Informática - Master Hotéis
ICQ Externo: 19398317
ICQ Interno: 1002
Linux user#: 288006