Re: [Gnumed-devel] Re: Gnumed-devel Digest, Vol 6, Issue 34

[Karsten Hilbert]

> The storage of the FK is neccessary to facilitate house keeping scripts for 
> later updates, since apparently identical referenced data can belong to 
> different foreign keys.
Unfortunately, yes:

create table state (
    id serial primary key,
    id_country integer references iso_codes_country(id),
    code varchar(20),
    "name" varchar(255),
    unique (id_country, code, "name")
);

create table address (
    id serial primary key,
    fk_code varchar(20) references state(code),
    fk_name varchar(255) references state(name),
    ...
);

Problems:

fk_code and fk_name don't work since there are no unique
constraints on the referenced columns in table state. Also,
there can't be because they ARE NOT unique - hence the
multiple column unique constraint in the state table.

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346