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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
