I want to add some notes
> create table diagnosis (
> pk serial primary key,
> fk_patient integer
> not null
> references patient(pk)
> on update cascade
> on delete cascade,
> narrative text
> not null,
> unique(fk_patient, narrative)
> );
1) a sequence generates INT8 values (in general) and you have INT4 field to
refer to a serial field.
2) narrative TEXT is very bad to check uniquness becauce it is non-formal
human-generated native-language text. so it contains missprints, it may be
rephrased many ways with the same meaning.
3) afaik a diagnosis doesn't belong to a patient,
it belongs to a History, and History is marked with a date and status and
belongs to a patient.
do not treat my words as The Truth.