Thread: problem with parent/child table and FKs
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
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
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
On Thursday, April 21, 2011 4:36:51 am Karsten Hilbert wrote:
> Does anyone have any suggestions regarding the below ?
The only thing I can come up with is to eliminate the FK :
fk_code integer not null
references code_root(pk_code_root)
on update restrict
on delete restrict
on lnk_codes2epi and replace it with a trigger that essentially does the same
thing ; check for presence of pk_code_root.
>
> Thanks,
> Karsten
>
--
Adrian Klaver
adrian.klaver@gmail.com
On 18 Apr 2011, at 15:53, Karsten Hilbert wrote:
> 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, ...).
I think your best bet is to not rely on inheritance here. If you instead reference code_root from icd10 using (code,
term),you end up with the same results, while you then only need to reference code_root from your lnk_codes2epi table.
> 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);
So this would become:
create table code_root (
pk_code_root serial primary key,
code text not null,
term text not null,
UNIQUE (code, term)
);
create table icd10 (
pk serial primary key,
code text not null,
term text not null,
version text not null,
FOREIGN KEY (code, term) REFERENCES code_root (code, term)
);
This does rely on the combination of (code, term) being unique in code_root. If it's not, you would need an extra table
withjust every unique combination of (code, term) that both code_root and icd10 would reference.
BTW, do you really need those artificial PK's? If not, you may well be better off dropping them. That way (code, term)
couldbe your PK instead. I don't know enough about your data to make more than a guess though, I just get itchy when I
seesuch designs ;)
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:737,4db0665111731275120228!
On Thu, Apr 21, 2011 at 07:53:04AM -0700, Adrian Klaver wrote:
> On Thursday, April 21, 2011 4:36:51 am Karsten Hilbert wrote:
> > Does anyone have any suggestions regarding the below ?
>
> The only thing I can come up with is to eliminate the FK :
> fk_code integer not null
> references code_root(pk_code_root)
> on update restrict
> on delete restrict
> on lnk_codes2epi and replace it with a trigger that essentially does the same
> thing ; check for presence of pk_code_root.
I feared as much. I hoped to get around that somehow but
what you suggest doesn't sound half bad, actually.
I had been thinking to do the typical master-detail tables
for the coding systems instead of the inheritance:
table generic_code
pk serial primary key
code
term
system
table icd10
pk serial primary key
fk_generic_code
references generic_code(pk)
icd10_extra_field
table icd9
pk serial primary key
fk_generic_code
references generic_code(pk)
icd9_extra_field
table disease
pk serial primary key
description
table lnk_code2disease
pk serial primary key
fk_generic_code
references generic_code(pk)
fk_disease
references disease(pk)
But then I'd have to write even more triggers making sure
that rows in, say, the icd10 table don't link to rows in the
generic_code table whose .system <> 'icd10'.
I can't put the .system column into the icd10/icd9/...
tables either because I need that column in generic_code to
ensure:
unique(code, system)
Sheesh :-)
I suppose my underlying problem is that PostgreSQL's
inheritance is not intended to support polymorphism which is
what I seem to be trying to do - link diseases to
polymorphic code tables.
I can't easily think of a better relational solution,
though. The real world requirement for polymorphism is
surely there.
I guess I'll go with your solution unless someone comes up
with a better idea yet.
Karsten
--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On Thu, Apr 21, 2011 at 07:15:38PM +0200, Alban Hertroys wrote: > BTW, do you really need those artificial PK's? If not, you > may well be better off dropping them. That way (code, term) > could be your PK instead. I don't know enough about your > data to make more than a guess though, I just get itchy when > I see such designs ;) Well, coding system content changes at the whims of governments, professional boards, the winds of change, and the hair color of the director of WHO... Karsten -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On Thu, Apr 21, 2011 at 07:15:38PM +0200, Alban Hertroys wrote: > > 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, ...). > > I think your best bet is to not rely on inheritance here. > If you instead reference code_root from icd10 using (code, > term), you end up with the same results, while you then only > need to reference code_root from your lnk_codes2epi table. ... > So this would become: > > create table code_root ( > pk_code_root serial primary key, > code text not null, > term text not null, > UNIQUE (code, term) > ); > > create table icd10 ( > pk serial primary key, > code text not null, > term text not null, > version text not null, > FOREIGN KEY (code, term) REFERENCES code_root (code, term) > ); > > This does rely on the combination of (code, term) being > unique in code_root. If it's not, It is not, because there can, for example, be several versions (depending on, say, the country) and generations (evolving over time) of "one and the same" coding system. > you would need an extra > table with just every unique combination of (code, term) > that both code_root and icd10 would reference. Or else I could, additionally, put a .system_version column in both tables and add that into the foreign key. The downside would be that I now have a slow(er) foreign key consisting of three text fields which, in addition, each duplicate information (however, the database helps me to keep those duplicates in sync by way of ON UPDATE/DELETE CASCADE). I would then need to add an ON INSERT trigger to each of the icd10, ... tables such that INSERTS into them get propagated to code_root. Then it'd be wise to disallow user level INSERTs/UPDATEs/DELETEs on code_root. Karsten -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346