Re: surrogate key or not?

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.