Re: Lookup tables - Mailing list pgsql-general

> On 5 Feb 2025, at 21:33, Thiemo Kellner <thiemo@gelassene-pferde.biz> 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
getsan 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
onsubmission (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
anythingbeforehand. 
>> 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
semanticallyALWAYS 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
thelavishly 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
surrogatekey 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
wavesof 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
youhave to do it in applications. 

Anyway - let’s agree to disagree :)

—
Michal