Re: problem with parent/child table and FKs

On Thu, Apr 21, 2011 at 01:36:51PM +0200, Karsten Hilbert wrote:

> Does anyone have any suggestions regarding the below ?

If you guys happen to think this could be a
"please-do-my-class-assignment-for-me" question -- I'd be
glad to read up on things if someone clues me in on the
relevant keywords to look up !

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

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