> On 5 Feb 2025, at 21:33, Thiemo Kellner <thi...@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 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
