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