In our in house MRP system we have "id" for every record. Customers have keys like c_idXXXX and sales orders have keys like pv002002.
For us it is very good that these numbers are human readable, (no UUID) because we use them to find records
These private key fields are defined as "number" (although they contain text sometimes, like pv002002 "pedido de venda 002002")
When I am in the sales order table PV I define a foreign key field, where the customers id gets written in. These fields I historically defined as text fields.
Now... this setup gives me trouble when doing SQL queries. There the two fields should be of the same type.
However to change everything (both sides, the private and the foreign key fields) to text or to number seems risky.
What will happen if I change this fields now from text to number? Can I do that without risking data integrity?
What is best practice to define ID fields?
What is best practice to make this change (change field formats in private keys and foreign keys fields in many tables without loosing info).