problem with parent/child table and FKs - Mailing list pgsql-general

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