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

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@postgresql.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