Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.300.87.4.3\))
Subject: Re: Lookup tables
From: =?utf-8?Q?Micha=C5=82_K=C5=82eczek?= <michal@kleczek.org>
In-Reply-To: <8a118426-7b8a-4d8b-a22b-3bdee2e5e187@gelassene-pferde.biz>
Date: Wed, 5 Feb 2025 22:19:30 +0100
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <878194FA-E9F8-4688-9904-06B8D2A4DD5C@kleczek.org>
References: <c0fe3bc0-efb2-4648-ac9a-435601acc2bf@gelassene-pferde.biz>
 <628D4022-5365-4D00-810B-10349A90874A@kleczek.org>
 <64603f36-b6d5-469b-8d6d-30376842241b@gelassene-pferde.biz>
 <3A08C6E4-8B03-4D24-BAA7-53860A66C1AF@kleczek.org>
 <8a118426-7b8a-4d8b-a22b-3bdee2e5e187@gelassene-pferde.biz>
To: Thiemo Kellner <thiemo@gelassene-pferde.biz>
Archived-At: 
 <https://www.postgresql.org/message-id/878194FA-E9F8-4688-9904-06B8D2A4DD5C%40kleczek.org>
Precedence: bulk


> On 5 Feb 2025, at 21:33, Thiemo Kellner <thiemo@gelassene-pferde.biz> =
wrote:
>=20
>=20
> El 05-02-25 a las 13:55, Micha=C5=82 K=C5=82eczek escribi=C3=B3:
>>> A) Your release changed the sementics of the record 3. It's meaning =
changed. I cannot recommend doing that.
>> That=E2=80=99s what using natural keys and FK=E2=80=99s restricting =
their changes guarantee: no (accidental) changes to meaning of data.
>> Even with cascading updates you still have transactional semantics =
(ie. the user selects what=E2=80=99s on the screen or gets an error).
> Sorry, that is utter nonsense. You cannot ever guarantee an update =
does not mess up the semantics on the updated field, change the meaning. =
Y

But you can guarantee that if you change the value of the key after the =
user displays it - the user will get an error on submission (whereas =
with the surrogate key it would happily proceed without user noticing).

> ou would need a check constraint which in it turn needs to get set up =
where one can mess up things.
>>> B) If you absolutely must change the semantic, put your application =
into maintenance mode in which noone can select anything beforehand.
>> All this error prone hassle and downtime can be avoided with natural =
keys and guarantees that DBMS gives you.
> And I thought you would have denied the need of changing semantics =
above. And no, changing your natural keys semantically ALWAYS requires =
downtime to make sure you do not run into the race condition described =
above.

How so? The user is going to get FK violation - you do not need any =
downtime to make sure users don=E2=80=99t submit wrong values.

>>> If the maintenance would just correct the typo from GREE to GREEN, =
nothing would happen. Yor customer still ordered the lavishly green =
E-Bike her hear ever desired.
>> The question is: how do you _ensure_ that?
> Ensure, the update goes from GREE to GREEN? You cannot, simple as =
that. You just can minimize the risk by testing, testing, testing.

You can also simply disallow updates with FK constraint eliminating =
risk.

> But that holds equally true for the business key of a surrogate key =
table as natural key table. That's why the surrogate key is such an =
elegant construct. You can change business key of the record with id 3 =
from GREE to GREEN, VERT, GR=C3=9CN, VERDE or AS=C3=A9LDHK()*NSLDFHP)(*Z =
. It keeps its meaning of the perception of the human eye of =
electromagnetic waves of the wavelength roughly between 495-570 nm =
(according to Wikipedia).

And why do you think unconstrained updating of business key is a good =
thing?
You must implement rules governing what can and what cannot be changed =
*somewhere* - not doing it in the database means you have to do it in =
applications.

Anyway - let=E2=80=99s agree to disagree :)

=E2=80=94
Michal=