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
anerror).
Sorry, that is utter nonsense. You cannot ever guarantee an update does
not mess up the semantics on the updated field, change the meaning. You
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 semantically ALWAYS requires
downtime to make sure you do not run into the race condition described
above.
>> 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. 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).
