El 05-02-25 a las 22:19, Michał Kłeczek escribió:
> 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).
As you very rightly say happily proceed, because that is, what you
actually want. The user chose GREE meaning green, which is updated in
the meantime to GREEN. All good. Your solution throws an error for nothing.
> 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.
Sorry, I was not aware, your aim is to bother the customer with FK
violation messages, if you can avoid it.
>> 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.
This is nothing that is specific to surrogate or natural keys. If one
disallow updates, one has to live with typos and everything.
>> 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?
I am not sure what is your take on "unconstrained". As already
mentioned, testing is the only constraint you can set up. And I am
neither sure where your problem is with updating unless you have an
update orgy because your ref-constraint goes on natural keys.
> 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.
That sounds adventurous. Maybe I am not seeing what you mean, but is
sound to me that you build a shadow database where you map your GREE to
GREEN in the application layer so the application can display the
correct value in the GUI, but no one has to update the core database.
And if there is a typo in the shadow data base you build another mapping
database on top of it?
> Anyway - let’s agree to disagree :)
Agreed :-)