Re: Problem with some keys

Apologies, I should have said you have no museo row with nomemuseo =
'MuseoVeronese' AND citte = 'Milano' (which is exactly what the error
message says).

IOW, you execute this statement:

update museo set nomemuseo='MuseoFantasma' where citta='Milano';

Since you changed the PK value in museo, the "ON UPDATE SET DEFAULT"
clause in the FK definition is triggered to change the value of
mostra.museo to the default value of museo.nomemuseo, which is
'MuseoVeronese':

update mostra set museo='MuseoVeronese' where citta='Milano'

Since there is no 'MuseoVeronese'/'Milano', that FK update fails.

What confused you is that you expected both columns in the FK to get set
to the defaults from the museo PK, but that's not what PostgreSQL is
doing. PostgreSQL says "the only column that changed in museo is
nomemuseo, so the only column that I will change in mostra is museo."
I'm not sure whether this is a bug or not - it is certainly confusing.

If you add MATCH FULL to your FK definition, it will behave the way you
expect. That said, this is strange design anyhow, because in the real
world that your db is modeling, 'Il genio di Da Vinci' did not magically
jump to Verona just because you changed the name of the museum - it is
still in Milano. So what you probably want is ON UPDATE CASCADE.

Personally, I never use cascaded updates/deletes (Google "cascade
update"+"bad idea" to see why). I always use internal keys (typically
automatically-generated integers) that never change, to maintain PK-FK
relationships. But this is one of those "religious issues" - I have
probably triggered a flame war by saying that.

A few more style points:

- Use VARCHAR instead of CHAR unless you have some compelling reason to
use CHAR.

- Using FLOAT for money is dangerous, because FLOAT cannot accurately
represent decimal fractions. Use NUMERIC (aka DECIMAL) instead.

- It makes life easier if you keep equivalent column names the same in
both tables wherever possible.

--
Peter Headland