public inbox for [email protected]  

help / color / mirror / Atom feed

From: Michał Kłeczek <[email protected]>
To: Thiemo Kellner <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Lookup tables
Date: Wed, 5 Feb 2025 22:19:30 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>



> On 5 Feb 2025, at 21:33, Thiemo Kellner <[email protected]> wrote:
> 
> 
> El 05-02-25 a las 13:55, Michał Kłeczek escribió:
>>> A) Your release changed the sementics of the record 3. It's meaning changed. I cannot recommend doing that.
>> That’s what using natural keys and FK’s 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’s 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’t 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ÜN, VERDE or ASéLDHK()*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’s agree to disagree :)

—
Michal

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Lookup tables
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox