Re: problem with parent/child table and FKs

Does anyone have any suggestions regarding the below ?

Thanks,
Karsten

On Mon, Apr 18, 2011 at 03:53:16PM +0200, Karsten Hilbert wrote:

> Hello all,
> 
> since (according to the docs) PostgreSQL does not propagate
> INSERTs from child tables unto parent tables the below does
> not work, unfortunately.
> 
> What is the suggested approach for this situation ? (there
> will be more tables like "icd10" holding other coding
> systems of fairly diverse nature but all of them sharing
> .code and .term: LOINC, ATC, ICPC-2, ICD-9, ...).
> 
> Thanks,
> Karsten
> (www.gnumed.de)
> 
> 
> begin;
> 
> create table code_root (
> 	pk_code_root serial primary key,
> 	code text not null,
> 	term text not null
> );
> 
> create table icd10 (
> 	pk serial primary key,
> 	version text not null
> ) inherits (code_root);
> 
> create table disease (
> 	pk serial primary key,
> 	disease_name text not null
> );
> 
> create table lnk_codes2epi (
> 	pk serial primary key,
> 	fk_disease integer not null
> 		references disease(pk)
> 		on update cascade
> 		on delete cascade,
> 	fk_code integer not null
> 		references code_root(pk_code_root)
> 		on update restrict
> 		on delete restrict
> );
> 
> insert into icd10 (code, term, version) values ('J99.9', 'Grippe', 'ICD-10-GM');
> insert into disease (disease_name) values ('URTI/flu');
> select * from code_root;
>
>  pk_code_root | code  |  term  
> --------------+-------+--------
>             1 | J99.9 | Grippe
> (1 Zeile)
> 
> select * from icd10;
>
>  pk_code_root | code  |  term  | pk |  version  
> --------------+-------+--------+----+-----------
>             1 | J99.9 | Grippe |  1 | ICD-10-GM
> (1 Zeile)
> 
> select * from disease;
>
>  pk | disease_name 
> ----+--------------
>   1 | URTI/flu
> (1 Zeile)
> 
> insert into lnk_codes2epi (fk_disease, fk_code) values (
> 	(select pk from disease where disease_name = 'URTI/flu'),
> 	(select pk_code_root from code_root where code = 'J99.9')
> );
> psql:x-check-delete.sql:47: ERROR:  insert or update on table "lnk_codes2epi" violates foreign key constraint "lnk_codes2epi_fk_code_fkey"
> DETAIL:  Key (fk_code)=(1) is not present in table "code_root".
> 
> rollback;

-- 
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346